Dgirth Posts - Answer Overflow

Dgirth

•Created by Dgirth on 10/24/2023 in #help

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!

24 replies

Gaming

Programming