Jonte
Jonte
DTDrizzle Team
Created by Jonte on 7/2/2023 in #help
Expand query from ID using .select()
Hi! I am trying to get the first name of a comment author, but only store the ID so the user can change their name. Here's my schema.ts
import { InferModel, relations } from "drizzle-orm";
import { pgTable, text, varchar, bigint, json } from "drizzle-orm/pg-core";
import { v4 } from "uuid";

export const users = pgTable("users", {
id: text("id").notNull().default(v4()).primaryKey(),
firstName: varchar("first_name"),
lastName: varchar("last_name"),
email: text("email"),
password: text("password"),
});

export const comments = pgTable("comments", {
id: text("id").notNull().default(v4()).primaryKey(),
authorId: text("author_id")
.notNull()
.references(() => users.id),
text: text("text"),
created: bigint("created", {
"mode": "number",
}).notNull(),
replies: json("replies").notNull().default([]),
post: text("post").notNull(),
});

export const commentsRelations = relations(comments, ({ one }) => ({
author: one(users, { fields: [comments.authorId], references: [users.id] }),
}));

export type User = InferModel<typeof users>;
export type NewUser = InferModel<typeof users, "insert">;
export type Comment = InferModel<typeof comments>;
import { InferModel, relations } from "drizzle-orm";
import { pgTable, text, varchar, bigint, json } from "drizzle-orm/pg-core";
import { v4 } from "uuid";

export const users = pgTable("users", {
id: text("id").notNull().default(v4()).primaryKey(),
firstName: varchar("first_name"),
lastName: varchar("last_name"),
email: text("email"),
password: text("password"),
});

export const comments = pgTable("comments", {
id: text("id").notNull().default(v4()).primaryKey(),
authorId: text("author_id")
.notNull()
.references(() => users.id),
text: text("text"),
created: bigint("created", {
"mode": "number",
}).notNull(),
replies: json("replies").notNull().default([]),
post: text("post").notNull(),
});

export const commentsRelations = relations(comments, ({ one }) => ({
author: one(users, { fields: [comments.authorId], references: [users.id] }),
}));

export type User = InferModel<typeof users>;
export type NewUser = InferModel<typeof users, "insert">;
export type Comment = InferModel<typeof comments>;
And here's my query:
let gotComments = await db
.select()
.from(comments)
.where(eq(comments.post, post))
.orderBy(sql`${comments.id} desc`)
.limit(100)
let gotComments = await db
.select()
.from(comments)
.where(eq(comments.post, post))
.orderBy(sql`${comments.id} desc`)
.limit(100)
But instead of returning the user's ID I ofcourse want to return the user's first name in the author field.
3 replies