23 replies

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!

Drizzle Team•3y ago•

23 replies

Dgirth

Filter for articles with at least one comment

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)),
  ...
})

Filter for articles with at least one comment

Similar Threads

Filter for articles with at least one comment

Similar Threads

Similar Threads

Similar Threads