Drizzle Team•8mo ago

How to use db.query.comments.findFirst with WHERE on comments.post.auhor.name?

Using the schema example from the doc: https://orm.drizzle.team/docs/rqb#select-filters I'm trying to find the nicest way to write db.query.comments.findFirst for: Find a comment by specific comment.text AND specific comment.post.author.name ? Schema:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  content: text('content'),
  authorId: integer('author_id'),
});

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

export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  text: text('text'),
  authorId: integer('author_id'),
  postId: integer('post_id'),
  createdAt: timestamp("createdAt").notNull().defaultNow(),
});

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  content: text('content'),
  authorId: integer('author_id'),
});

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

export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  text: text('text'),
  authorId: integer('author_id'),
  postId: integer('post_id'),
  createdAt: timestamp("createdAt").notNull().defaultNow(),
});

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

So the question is, is this query possible with drizzle and usedb.query.comments.findFirst for: Find a comment by specific comment.text AND specific comment.post.author.name ? I'm aware comment's text and author (user) name are not unique like user id or comment Id, let's just ignore that for now.

Drizzle ORM - Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

8 Replies

JulianOP•8mo ago

I can think of something like this but got stuck from here

const textToFind = "im a comment";
const postAuthorNameToFind = "Julian";

db.query.commments.findFirst({
  where: (table, { eq }) =>
    and(
      eq(commments.text, textToFind),
      eq(WhatDoIPutHere, postAuthorNameToFind),
    )
  ),
  columns: {
    id: true,
    text: true,
    createdAt: true
  },
  with: {
    post: {
      with: {
        author: {
          columns: {
            // Need to match this with postAuthorNameToFind (Julian)
            name: true,
          }
        }
      }
    }
  } 
})

const textToFind = "im a comment";
const postAuthorNameToFind = "Julian";

db.query.commments.findFirst({
  where: (table, { eq }) =>
    and(
      eq(commments.text, textToFind),
      eq(WhatDoIPutHere, postAuthorNameToFind),
    )
  ),
  columns: {
    id: true,
    text: true,
    createdAt: true
  },
  with: {
    post: {
      with: {
        author: {
          columns: {
            // Need to match this with postAuthorNameToFind (Julian)
            name: true,
          }
        }
      }
    }
  } 
})

Sillvva•8mo ago

Querying by nested tables in the relational query builder isn't currently easy.

const textToFind = "im a comment";
const postAuthorNameToFind = "Julian";

db.query.commments.findFirst({
  where: (table, { eq }) =>
    and(
      eq(commments.text, textToFind),
      // from here
      exists(
        db.select()
          .from(posts)
          .innerJoin(users, eq(posts.authorId, users.id))
          .where(
            and(
              eq(posts.id, table.postId),
              eq(users.name, postAuthorNameToFind)
            )
          )
      )
      // to here
    )
  ),
  columns: {
    id: true,
    text: true,
    createdAt: true
  },
  with: {
    post: {
      with: {
        author: {
          columns: {
            // Need to match this with postAuthorNameToFind
            name: true,
          }
        }
      }
    }
  } 
})

const textToFind = "im a comment";
const postAuthorNameToFind = "Julian";

db.query.commments.findFirst({
  where: (table, { eq }) =>
    and(
      eq(commments.text, textToFind),
      // from here
      exists(
        db.select()
          .from(posts)
          .innerJoin(users, eq(posts.authorId, users.id))
          .where(
            and(
              eq(posts.id, table.postId),
              eq(users.name, postAuthorNameToFind)
            )
          )
      )
      // to here
    )
  ),
  columns: {
    id: true,
    text: true,
    createdAt: true
  },
  with: {
    post: {
      with: {
        author: {
          columns: {
            // Need to match this with postAuthorNameToFind
            name: true,
          }
        }
      }
    }
  } 
})

Sillvva•8mo ago

Alternatively

db
  .select({
    id: comments.id,
    text: comments.text
    createdAt: comments.createdAt,
    post: {
      ...getTableColumns(posts),
      author: {
        name: users.name
      }
    }
  })
  .from(comments)
  .innerJoin(posts, eq(posts.id, comments.postId))
  .innerJoin(users, eq(users.id, posts.authorId))
  .where(
    and(
      eq(users.name, postAuthorNameToFind),
      eq(comments.text, textToFind)
    )
  )

db
  .select({
    id: comments.id,
    text: comments.text
    createdAt: comments.createdAt,
    post: {
      ...getTableColumns(posts),
      author: {
        name: users.name
      }
    }
  })
  .from(comments)
  .innerJoin(posts, eq(posts.id, comments.postId))
  .innerJoin(users, eq(users.id, posts.authorId))
  .where(
    and(
      eq(users.name, postAuthorNameToFind),
      eq(comments.text, textToFind)
    )
  )

https://arc.net/l/quote/dqiycnpc

Quote from “Drizzle ORM - Joins”

JulianOP•8mo ago

I see, thank you so much for help @Sillvva !!! I think the first solution is nicer to write than the alternative solution. I think the alternative solution has an issue if it gets even more complicated. Let's say there's likes table, one-to-many to comments, with userId and commentId . Now if I want to pull likes as well. The first approach is pretty easy to modify.

const textToFind = "im a comment";
const postAuthorNameToFind = "Julian";

db.query.commments.findFirst({
  where: (table, { eq }) =>
    and(
      eq(commments.text, textToFind),
      // from here
      exists(
        db.select()
          .from(posts)
          .innerJoin(users, eq(posts.authorId, users.id))
          .where(
            and(
              eq(posts.id, table.postId),
              eq(users.name, postAuthorNameToFind)
            )
          )
      )
      // to here
    )
  ),
  columns: {
    id: true,
    text: true,
    createdAt: true
  },
  with: {
    likes: {
      columns: {
        id: true,
        userId: true,
        createdAt: true
      }
    }
    post: {
      with: {
        author: {
          columns: {
            // Need to match this with postAuthorNameToFind
            name: true,
          }
        }
      }
    }
  } 
})

const textToFind = "im a comment";
const postAuthorNameToFind = "Julian";

db.query.commments.findFirst({
  where: (table, { eq }) =>
    and(
      eq(commments.text, textToFind),
      // from here
      exists(
        db.select()
          .from(posts)
          .innerJoin(users, eq(posts.authorId, users.id))
          .where(
            and(
              eq(posts.id, table.postId),
              eq(users.name, postAuthorNameToFind)
            )
          )
      )
      // to here
    )
  ),
  columns: {
    id: true,
    text: true,
    createdAt: true
  },
  with: {
    likes: {
      columns: {
        id: true,
        userId: true,
        createdAt: true
      }
    }
    post: {
      with: {
        author: {
          columns: {
            // Need to match this with postAuthorNameToFind
            name: true,
          }
        }
      }
    }
  } 
})

Second approach is probably harder, you probably have to do join with likes table, and then reconstruct the object to make it return the object similarly to the first approach. Hopefully there are more features coming for this in the future, again, thank you so much!

Sillvva•8mo ago

RQB (relational query builder) is better for one-to-many and many-to-many for sure, because of the structure of the output type. For one-to-one and many-to-one, it's pretty much the same.

JulianOP•8mo ago

Yeah exactly, thank you so much for the help! I've heard there's new ideas for RQB going on for next major versions, excited to see what they come up with! Ah found it

JulianOP•8mo ago

https://github.com/drizzle-team/drizzle-orm/discussions/2316

GitHub

Relational API v2 · drizzle-team drizzle-orm · Discussion #2316

We've launched Drizzle Relational Queries exactly a year ago and it's time to ship a fundamental upgrade. We've gathered a massive amount of valuable feedback from the community and goi...

Sillvva•8mo ago

Yep. I'm following it 🙂

Gaming

Programming

How to use db.query.comments.findFirst with WHERE on comments.post.auhor.name?