N Nested Relations

MMoltenFuzzy6/3/2023
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.
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;
I want my data returned like this but im not sure how to fix my query to do that.
[
{
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: [] },
],
},
]
THANK YOU!!! Problem resolved, will post my solution later
Iiukea6/3/2023
Please do
MMoltenFuzzy6/3/2023
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.
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);
}),
the result is this
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: [] },

]
Iiukea6/4/2023
Can you post you schema
MMoltenFuzzy6/4/2023
sure
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
}
updated version includes user
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";`;

Looking for more? Join the community!

Want results from more Discord servers?
Add your server
Recommended Posts
A question on procedure design.I would like to know if it is best to try combine procedures or keep them separated. I don't want toMigrating to supabase from create-t3-turboHey guys! I'm migrating a create-t3-turbo app to the supabase create-t3-turbo template, and I'm a biSystem dark mode not detected when using darkMode: "media" in tailwind.config.tsHello! I am building a simple starter project using Tailwind and `shadcn-ui` components. I want the What is wrapping a next server action in startTransition doing?This was brought up in another question. I came up with the following explination, however this is jCan I call tRPC procedure inside another procedure?Something like this: ```js export const mainRouter = createTRPCRouter({ create: privateProcedure Anyone know how to go about using Tailwind Animation to animate a linethrough on hover?Or framer motion.Task scheduler infra?I'm looking for something that can do the following (or do essentially the same thing). It feels likNeed suggestions for better infra CICDalright, so for this project i use docker containers for everything. my compose file has a redis cacConditional render, why can't it be done?I understand the reason this happens it that *something* doesn't understand that the Loader already do something once server action is completedIs there a way to achieve this (see title)? I’m using useTransition to start the action and need to How to use discord.js in Nextjs app dirHello, I am trying to use discord.js in my app but getting alot of errors, is this even possible?: `best way to fetch data in nextjsI was wondering what's the best way at the moment to query for external apis in NextJS? I know that @next/font turbo t3You might be using incompatible version of `@next/font` (13.4.4) and `next` (13.1.6). what version sTypescript issue causing trpc functions to not have type checkingHey, I'm in a create-t3-turbo app and have been banging my head against the wall trying to figure ouClerk middleware breaks Discord URL preview (embed)Anyone know how to fix this ...?T3 env lint error on Github CIRun npm run lint > chirp@0.1.0 lint > next lint ❌ Invalid environment variables: { PUSHER_APP_IDT3 Expo - react-native-gesture-handlerAnyone used this package with t3 turbo on expo app? I'm getting a package not found error on compileServer side caching with Next APIUnsure if this pattern is common, or if I am approaching this incorrectly. I am using an api in my