Drizzle Team•15mo ago

Filter for articles with at least one comment

I am trying to migrate to Drizzle from Prisma and I am having issue with being able to query for all Articles that has at least one Comment as the title says. Here's a simplified version of my schema:

export const articles = sqliteTable("Articles", {
  id: text("id").primaryKey().notNull(),
  body: text("body"),
  ...
})

export const articlesRelations = relations(articles, ({many}) => ({
  comments: many(comments),
  ...
}))

export const comments = sqliteTable("comments", {
  body: text("body),
  articleId: text("articleId").notNull().references(() => articles.id, { onDelete: "cascade", onUpdate: "cascade" }),
  ...
})

export const commentsRelations = relations(comments, ({one}) => ({
  article: one(articles, {
    fields: [comments.articleId],
    references: [articles.id]
  })
}))

export const articles = sqliteTable("Articles", {
  id: text("id").primaryKey().notNull(),
  body: text("body"),
  ...
})

export const articlesRelations = relations(articles, ({many}) => ({
  comments: many(comments),
  ...
}))

export const comments = sqliteTable("comments", {
  body: text("body),
  articleId: text("articleId").notNull().references(() => articles.id, { onDelete: "cascade", onUpdate: "cascade" }),
  ...
})

export const commentsRelations = relations(comments, ({one}) => ({
  article: one(articles, {
    fields: [comments.articleId],
    references: [articles.id]
  })
}))

I have tried a bunch of iterations of the following:

const articles = await db.query.articles.findMany({
  where: (article, {exists, eq}) => exists(db.select().from(comments).where(eq(comments.articleId, article.id)),
  ...
})

const articles = await db.query.articles.findMany({
  where: (article, {exists, eq}) => exists(db.select().from(comments).where(eq(comments.articleId, article.id)),
  ...
})

Except this, gives me a syntax error at "(" ........ I hope someone can help me out understand my mistake and misunderstanding here as if I can handle this, every other query filter should easily be migrated from Prisma in my code base. (hopefully!) Thanks in advance!

11 Replies

Angelelz•15mo ago

There a couple ways to do this The easiest is using the RQB like you

const articles = await db.query.articles.findMany({
  where: inArray(articles.id, db.select({ id: comments.articleId }).from(comments)),
  ..
})

const articles = await db.query.articles.findMany({
  where: inArray(articles.id, db.select({ id: comments.articleId }).from(comments)),
  ..
})

Another one is with an inner join

const articles = await db.selectDistinct({ articles })
  .from(comments)                 // <--- notice how you select from comments
  .innerJoin(articles, eq(comments.articleId, articles.id))

const articles = await db.selectDistinct({ articles })
  .from(comments)                 // <--- notice how you select from comments
  .innerJoin(articles, eq(comments.articleId, articles.id))

You can also do it with a subquery:

const articles = await db.select({ articles })
  .from(articles)
  .where(inArray(articles.id, db.select({id: comments.articleId}).from(comments)))

const articles = await db.select({ articles })
  .from(articles)
  .where(inArray(articles.id, db.select({id: comments.articleId}).from(comments)))

DgirthOP•15mo ago

Thanks a lot, the first simple option worked fantasticly! I have a follow up question concerning the subquery option. When would this be a better option? Is it perhaps in conjunction with prepared statements or something? Having serveral options is fantastic but even better if I would get an understanding of when 🙂

Angelelz•15mo ago

The problem I see with this approach is that it probably won't scale well, as the comments get more numerous the array in which you have to search will be a lot larger BTW, the first and third approach are equivalent, notice how they use exactly the same inArray

DgirthOP•15mo ago

yep

Angelelz•15mo ago

You'll have to run for benchmarks if you want to be sure with one you should use I think if you use selectDistict in the subquery it might be more efficient Run select analyze on the resulting queries to see how bad they are

DgirthOP•15mo ago

Alright, just feels wierd fetching articles from comments. oH, how would I do that?

Angelelz•15mo ago

sorry not select analyze, explain analyze Oh wait, in sqlite it's explain query plan https://www.sqlite.org/eqp.html

DgirthOP•15mo ago

I'll check that out, thanks! I suppose it might not be outrageous to add some boolean flag or count on the articles table for when there are comments perhaps. Making this query filter even easier

Angelelz•15mo ago

Don't optimize too early This would be worrisome after 500_000 comments I guess

DgirthOP•15mo ago

Thanks for the warning. In fact, these tables are not actually articles and comments, it's about things that I don't ever reach the 100_000'nds. Thank you very much Angelelz, taken down all your answers as reference notes 🙂 You're brilliant!

christrading•14mo ago

Is there an analyze command for Drizzle (and SQLite better-sqlite3)?

Gaming

Programming

Filter for articles with at least one comment