nested query
1 : i want just get comments not have reply how do it and i want just get comments have reply :
2 : i want just get comments with storeId example 1 (have relation one to many product to comment and product have storeId) :
schema :
export const comments = pgTable("comments", {
id: serial("id").primaryKey(),
text: varchar("text", { length: 191 }),
createdAt: timestamp("createdAt").defaultNow(),
authorId: integer("authorId").notNull(),
productId: integer("productId").notNull(),
storeId: integer("storeId"),
isFaq: boolean("isFaq").notNull().default(false),
isOwner: boolean("isOwner").notNull().default(false),
})
export const replies = pgTable("replies", {
id: serial("id").primaryKey(),
text: varchar("text", { length: 191 }),
createdAt: timestamp("createdAt").defaultNow(),
authorId: integer("authorId").notNull(),
isOwner: boolean("isOwner").notNull().default(false),
commentId: integer("commentId").notNull(),
})
export const commentsRelations = relations(comments, ({ many, one }) => ({
commentVotes: many(commentVotes),
product: one(products, {
fields: [comments.productId],
references: [products.id],
}),
reply: many(replies)
}))
export const replyRelations = relations(replies, ({ many, one }) => ({
comments: one(comments, {
fields: [replies.commentId],
references: [comments.id],
}),
}))
2 : i want just get comments with storeId example 1 (have relation one to many product to comment and product have storeId) :
schema :
export const comments = pgTable("comments", {
id: serial("id").primaryKey(),
text: varchar("text", { length: 191 }),
createdAt: timestamp("createdAt").defaultNow(),
authorId: integer("authorId").notNull(),
productId: integer("productId").notNull(),
storeId: integer("storeId"),
isFaq: boolean("isFaq").notNull().default(false),
isOwner: boolean("isOwner").notNull().default(false),
})
export const replies = pgTable("replies", {
id: serial("id").primaryKey(),
text: varchar("text", { length: 191 }),
createdAt: timestamp("createdAt").defaultNow(),
authorId: integer("authorId").notNull(),
isOwner: boolean("isOwner").notNull().default(false),
commentId: integer("commentId").notNull(),
})
export const commentsRelations = relations(comments, ({ many, one }) => ({
commentVotes: many(commentVotes),
product: one(products, {
fields: [comments.productId],
references: [products.id],
}),
reply: many(replies)
}))
export const replyRelations = relations(replies, ({ many, one }) => ({
comments: one(comments, {
fields: [replies.commentId],
references: [comments.id],
}),
}))
