使用Prisma原生查询递归获取嵌套评论遇报错求助

错误原因 #

问题出在两个核心点：

1. 表名不匹配：Prisma默认会将模型名转换为复数小写的数据库表名，比如Comment模型对应的表名是comments，但你在SQL查询中使用了"Comment"，数据库找不到对应表，因此抛出relation "comment" does not exist错误。
2. 结果结构不符合预期：原查询通过LEFT JOIN关联回复会返回扁平化的重复数据，无法直接得到嵌套的评论树结构。

修复方案 #

第一步：统一表名 #

两种方式任选其一：

• 方式1：修改SQL中的表名：将所有"Comment"替换为"comments"（Prisma默认生成的复数表名）。
• 方式2：强制模型表名：在Prisma的Comment模型中添加@@map属性，让数据库表名与模型名一致：

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)
  // 你的其他字段...
  @@map("Comment") // 强制数据库表名为Comment
}

第二步：修正递归查询逻辑 #

修改查询语句，使用JSON_AGG聚合嵌套回复，直接得到结构化的评论树：

const articleId = input.articleId;

const query = Prisma.sql`
  WITH RECURSIVE comment_hierarchy AS (
    SELECT
      c.id,
      c.body,
      c.likesCount,
      c.createdAt,
      c.updatedAt,
      json_build_object(
        'id', u.id,
        'name', u.name,
        'username', u.username,
        'profile', u.profile
      ) AS author,
      c."parentId",
      1 as depth
    FROM "Comment" c -- 此处需与实际表名一致，用"comments"或"Comment"
    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,
      json_build_object(
        'id', u.id,
        'name', u.name,
        'username', u.username,
        'profile', u.profile
      ) AS author,
      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.author,
    COALESCE(
      (
        SELECT json_agg(reply)
        FROM (
          SELECT
            r.id,
            r.body,
            r.likesCount,
            r.createdAt,
            r.updatedAt,
            json_build_object(
              'id', ru.id,
              'name', ru.name,
              'username', ru.username,
              'profile', ru.profile
            ) AS author
          FROM "Comment" r
          INNER JOIN "User" ru ON r."userId" = ru.id
          WHERE r."parentId" = ch.id
          ORDER BY r.createdAt DESC
        ) AS reply
      ),
      '[]'::json
    ) AS replies
  FROM comment_hierarchy ch
  WHERE ch."parentId" IS NULL -- 仅返回顶级评论，嵌套回复已聚合
  ORDER BY ch.createdAt DESC;
`;

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

关键优化点 #

• 用json_build_object将作者信息打包为JSON对象，避免字段名冲突。
• 通过json_agg将每个评论的所有回复聚合成数组，直接得到嵌套结构，无需额外处理。
• 递归CTE遍历所有层级的评论，最终仅返回顶级评论，嵌套回复已包含在replies字段中。

内容的提问来源于stack exchange，提问作者Ujen Basi