8 replies

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.

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!!!

TTC

Theo's Typesafe Cult•3y ago•

8 replies

MoltenFuzzy

N Nested Relations

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!!!

N Nested Relations

Similar Threads

N Nested Relations

Similar Threads

Similar Threads

Similar Threads