Drizzle TeamDT
Drizzle Team•2y ago•
9 replies
Julian

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],
  }),
}));

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.
Was this page helpful?