Retrieving nested comments with recursion in Prisma raw query

Question

I am working on a project where I need to retrieve nested comments along with their authors using Prisma raw queries. I have a Comment model defined in my schema, and I want to fetch all the comments related to a specific article, including their nested replies.

Here is my Comment model in Prisma:

model Comment {
  id         String   @id @default(cuid())
...,
  parent     Comment? @relation("CommentToComment", fields: [parentId], references: [id])
  parentId   String?
  replies    Comment[] @relation("CommentToComment")
  repliesCount Int     @default(0)
...
  }

I have tried to retrieve the comments using the following Prisma raw query with recursion:

// My attempted code
const articleId = input.articleId; // Assuming you have the articleId value

const query = Prisma.sql`
  WITH RECURSIVE comment_hierarchy AS (
    SELECT
      c.id,
      c.body,
      c.likesCount,
      c.createdAt,
      c.updatedAt,
      u.id as userId,
      u.name,
      u.username,
      u.profile,
      c."parentId",
      1 as depth
    FROM "Comment" c
    INNER JOIN "User" u ON c."userId" = u.id
    WHERE c."articleId" = ${articleId} AND c."parentId" IS NULL
  
    UNION ALL
  
    SELECT
      c.id,
      c.body,
      c.likesCount,
      c.createdAt,
      c.updatedAt,
      u.id as userId,
      u.name,
      u.username,
      u.profile,
      c."parentId",
      ch.depth + 1
    FROM "Comment" c
    INNER JOIN "User" u ON c."userId" = u.id
    INNER JOIN comment_hierarchy ch ON c."parentId" = ch.id
  )
  
  SELECT
    ch.id,
    ch.body,
    ch.likesCount,
    ch.createdAt,
    ch.updatedAt,
    ch.userId,
    ch.name,
    ch.username,
    ch.profile,
    r.id as replyId,
    r.body as replyBody,
    r.likesCount as replyLikesCount,
    r.createdAt as replyCreatedAt,
    r.updatedAt as replyUpdatedAt,
    ru.id as replyUserId,
    ru.name as replyUserName,
    ru.username as replyUserUsername,
    ru.profile as replyUserProfile
  FROM comment_hierarchy ch
  LEFT JOIN "Comment" r ON ch.id = r."parentId"
  LEFT JOIN "User" ru ON r."userId" = ru.id
  ORDER BY ch.depth, ch.createdAt DESC;
`;

const result = await ctx.prisma.$queryRaw(query);

Unfortunately, this solution did not provide the expected results. I encountered the error relation "comment" does not exist.

Retrieving nested comments with recursion in Prisma raw query

0 Answers0