Drizzle Team•2y ago

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.

2 Replies

Hegar•2y ago

you have two options: 1. SQL like syntax

let gotComments = await db
    .select({
        userName: user.firstName
     })
    .from(comments)
    .where(eq(comments.post, post))
    .innerJoin(user, eq(user.id, comments.authorId))
    .orderBy(sql`${comments.id} desc`)
    .limit(100)

let gotComments = await db
    .select({
        userName: user.firstName
     })
    .from(comments)
    .where(eq(comments.post, post))
    .innerJoin(user, eq(user.id, comments.authorId))
    .orderBy(sql`${comments.id} desc`)
    .limit(100)

2. Query builder API

const result = await db.query.comments.findMany({
    with: {
        author: true            
    },
});

const result = await db.query.comments.findMany({
    with: {
        author: true            
    },
});

JonteOP•2y ago

Tysm!

Gaming

Programming

Expand query from ID using .select()