N Nested Relations
Hi im having trouble dealing with nested includes. I have a Comment and reply structure and replies are type Comments. Each Comment can have N replies and each reply(Comment type) can also have N replies. Prisma currently does not include support for this and I have N levels so I can't nest includes an unknown number of times. Right now I am trying a raw query but I suck at SQL and this is as far as i got.
I want my data returned like this but im not sure how to fix my query to do that.
THANK YOU!!!
WITH RECURSIVE comment_replies AS (
SELECT "Comment".id, "Comment".content
FROM "Post"
JOIN "Comment" ON "Post".id = "Comment"."postId"
WHERE "Post".id = ${input.postId}
UNION
--- recursive query (note it adds to the partial table "x")
SELECT c.id, c.content
FROM "Comment" c
INNER JOIN comment_replies cr ON c."parentCommentId" = cr.id
)
SELECT * FROM comment_replies;
WITH RECURSIVE comment_replies AS (
SELECT "Comment".id, "Comment".content
FROM "Post"
JOIN "Comment" ON "Post".id = "Comment"."postId"
WHERE "Post".id = ${input.postId}
UNION
--- recursive query (note it adds to the partial table "x")
SELECT c.id, c.content
FROM "Comment" c
INNER JOIN comment_replies cr ON c."parentCommentId" = cr.id
)
SELECT * FROM comment_replies;
[
{
id: "cli3zi32t0008hp6wf8vp0mdw",
content: "test",
replies: [
{ id: "cli3zi32t0008hp6wf8vp0mdx", content: "test", replies: [] },
{ id: "cli3zi32t0008hp6wf8vp0mda", content: "test", replies: [] },
],
},
{
id: "cli3zi32t0008hp6wf8vp0mdw",
content: "test",
replies: [
{ id: "cli3zi32t0008hp6wf8vp0mdx", content: "test", replies: [] },
{ id: "cli3zi32t0008hp6wf8vp0mda", content: "test", replies: [] },
],
},
]
[
{
id: "cli3zi32t0008hp6wf8vp0mdw",
content: "test",
replies: [
{ id: "cli3zi32t0008hp6wf8vp0mdx", content: "test", replies: [] },
{ id: "cli3zi32t0008hp6wf8vp0mda", content: "test", replies: [] },
],
},
{
id: "cli3zi32t0008hp6wf8vp0mdw",
content: "test",
replies: [
{ id: "cli3zi32t0008hp6wf8vp0mdx", content: "test", replies: [] },
{ id: "cli3zi32t0008hp6wf8vp0mda", content: "test", replies: [] },
],
},
]
5 Replies
Problem resolved, will post my solution later
Please do
Im not too sure how to have the sql query structure in that way so i just used a recursive CTE on my Comment table and wrote a recursive function to process the the result into the the structure of my interface.
the result is this
getAllFromPost: publicProcedure
.input(z.object({ postId: z.string() }))
.query(async ({ ctx, input }) => {
/*
instead go directly to comment table and search for comments with the postId
we know comments with null parentCommentIds are Top level comments
take those and recurse through them to until we reach comments with no replies in their list?
PROBLEM IS I CANT GET THE REPLIES LIST SINCE IT IS A RELATIONSHIP
*/
interface Comment {
id: string;
content: string;
parentCommentId: string | null;
replies?: Comment[];
}
const result = await ctx.prisma.$queryRaw<Comment[]>`
WITH RECURSIVE comment_replies AS (
SELECT id, content, "parentCommentId"
FROM "Comment"
WHERE "Comment"."parentCommentId" IS NULL
AND "Comment"."postId" = ${input.postId}
UNION ALL
SELECT c.id, c.content, c."parentCommentId"
FROM "Comment" c
INNER JOIN comment_replies cr ON cr.id = c."parentCommentId"
)
SELECT * FROM comment_replies;`;
// Helper function to recursively build the comment tree
function buildCommentTree(
comments: Comment[],
parent_id: string | null = null
): Comment[] {
const commentTree: Comment[] = [];
for (const comment of comments) {
if (comment.parentCommentId === parent_id) {
const childComments = buildCommentTree(comments, comment.id);
if (childComments.length > 0) {
comment.replies = childComments;
} else {
comment.replies = [];
}
commentTree.push(comment);
}
}
return commentTree;
}
return buildCommentTree(result);
}),
getAllFromPost: publicProcedure
.input(z.object({ postId: z.string() }))
.query(async ({ ctx, input }) => {
/*
instead go directly to comment table and search for comments with the postId
we know comments with null parentCommentIds are Top level comments
take those and recurse through them to until we reach comments with no replies in their list?
PROBLEM IS I CANT GET THE REPLIES LIST SINCE IT IS A RELATIONSHIP
*/
interface Comment {
id: string;
content: string;
parentCommentId: string | null;
replies?: Comment[];
}
const result = await ctx.prisma.$queryRaw<Comment[]>`
WITH RECURSIVE comment_replies AS (
SELECT id, content, "parentCommentId"
FROM "Comment"
WHERE "Comment"."parentCommentId" IS NULL
AND "Comment"."postId" = ${input.postId}
UNION ALL
SELECT c.id, c.content, c."parentCommentId"
FROM "Comment" c
INNER JOIN comment_replies cr ON cr.id = c."parentCommentId"
)
SELECT * FROM comment_replies;`;
// Helper function to recursively build the comment tree
function buildCommentTree(
comments: Comment[],
parent_id: string | null = null
): Comment[] {
const commentTree: Comment[] = [];
for (const comment of comments) {
if (comment.parentCommentId === parent_id) {
const childComments = buildCommentTree(comments, comment.id);
if (childComments.length > 0) {
comment.replies = childComments;
} else {
comment.replies = [];
}
commentTree.push(comment);
}
}
return commentTree;
}
return buildCommentTree(result);
}),
data: [
{
id: 'clifwdlup0016hpuohylng1ie',
content: '1',
parentCommentId: null,
replies: [
{
id: 'clifwdmxt0017hpuo3em7atry',
content: '2',
parentCommentId: 'clifwdlup0016hpuohylng1ie',
replies: []
}
]
}, { id: 'clifwfl750018hpuoib1191m0', content: 'he', parentCommentId: null, replies: [] },
]
data: [
{
id: 'clifwdlup0016hpuohylng1ie',
content: '1',
parentCommentId: null,
replies: [
{
id: 'clifwdmxt0017hpuo3em7atry',
content: '2',
parentCommentId: 'clifwdlup0016hpuohylng1ie',
replies: []
}
]
}, { id: 'clifwfl750018hpuoib1191m0', content: 'he', parentCommentId: null, replies: [] },
]
Can you post you schema
sure
updated version includes user
model Comment {
id String @id @default(cuid())
content String @db.VarChar(255)
published Boolean @default(false)
authorId String
author User @relation(fields: [authorId], references: [id])
postId String
post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
likesCount Int @default(0)
likedBy User[] @relation("likedComments")
dislikesCount Int @default(0)
dislikedBy User[] @relation("dislikedComments")
repliesCount Int @default(0)
replies Comment[] @relation("replies")
parentCommentId String?
parentComment Comment? @relation("replies", fields: [parentCommentId], references: [id])
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
model Comment {
id String @id @default(cuid())
content String @db.VarChar(255)
published Boolean @default(false)
authorId String
author User @relation(fields: [authorId], references: [id])
postId String
post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
likesCount Int @default(0)
likedBy User[] @relation("likedComments")
dislikesCount Int @default(0)
dislikedBy User[] @relation("dislikedComments")
repliesCount Int @default(0)
replies Comment[] @relation("replies")
parentCommentId String?
parentComment Comment? @relation("replies", fields: [parentCommentId], references: [id])
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
const result = await ctx.prisma.$queryRaw<Comment[]>`
WITH RECURSIVE comment_replies AS (
SELECT c.id, c.content, c."parentCommentId", c."authorId"
FROM "Comment" c
JOIN "User" u ON u.id = c."authorId"
WHERE c."parentCommentId" IS NULL
AND c."postId" = ${input.postId}
UNION ALL
SELECT c.id, c.content, c."parentCommentId", c."authorId"
FROM "Comment" c
INNER JOIN comment_replies cr ON cr.id = c."parentCommentId"
)
SELECT cr.id, cr.content, cr."parentCommentId", cr."authorId", u.username, u.image
FROM comment_replies cr
JOIN "User" u ON u.id = cr."authorId";`;
const result = await ctx.prisma.$queryRaw<Comment[]>`
WITH RECURSIVE comment_replies AS (
SELECT c.id, c.content, c."parentCommentId", c."authorId"
FROM "Comment" c
JOIN "User" u ON u.id = c."authorId"
WHERE c."parentCommentId" IS NULL
AND c."postId" = ${input.postId}
UNION ALL
SELECT c.id, c.content, c."parentCommentId", c."authorId"
FROM "Comment" c
INNER JOIN comment_replies cr ON cr.id = c."parentCommentId"
)
SELECT cr.id, cr.content, cr."parentCommentId", cr."authorId", u.username, u.image
FROM comment_replies cr
JOIN "User" u ON u.id = cr."authorId";`;