I want to use Prisma to store nested comments (like on reddit). How can I retrieve all the nested comments?

Question

I want to model nested comments, like on reddit. I'm using a one-to-many self relation like this:

model Comment {
  [...]
  parentId String?
  parent Comment? @relation("ParentChildren", fields: [parentId], references: [id])
  children Comment[] @relation("ParentChildren")
}

So each child is connected to its parent by parentId. The comments can be infinitely nested, parents can have grand-children, grand-grand-children, and so on.

My question is - is it possible to retrieve all of the parent comment's descendants in one query? How can I do that?

My goal is to get a json object that looks kinda like this:

  {
    comment: 'Lorem ipsum...',
    children: [
      {
        comment: 'Lorem ipsum...',
        children: [
          {
            comment: 'Lorem ipsum...',
            children: []
          },
          {
            comment: 'Lorem ipsum...',
            children: []
          },
        ],
      },
    ],
  },

Maddie

Unfortunately this isn't supported at the moment. The only possible workaround would be to implement it in SQL using rawQuery. There's an issue about it in the Prisma repo. I would suggest commenting your use-case over there so we can track the demand for this feature. – Speculate

@TasinIshmam Thanks, will do! – Maddie

Here is the solutions using Prisma what you need: github.com/WebDevSimplified/nested-comments – Scissors

Answer 1

model Comment {
  id        Int      @id @default(autoincrement())
  comment   String

  Children  Comment[] @relation("Comment_Children")
  parent    Comment?  @relation("Comment_Children", fields: [parent_id], references: [id])
  parent_id Int?
}

let comment = await prisma.comment.findMany({
  where: {
    parent_id: null,
  },
  include: {
    children: {
      include: {
        children: true,
      },
    },
  },
});

let comment = await prisma.comment.create({
  data: {
    comment: req.body.comment,
    parent_id: req.body.parent_id, // or `null` for parent comment
  },
  include: {
    Children: {
      include: {
        Children: true,
      },
    },
  },
});

Scissors

So awesome answer – Bhagavadgita

does this work for more than 2 levels deep (children of children aka. comments of comments) ? – Terceira

I guess this this fixed for 2 levels deep only. And it must be that way since you cannot estimate the deep, it may lead to performance issue. So best case is to include 2 levels only, and user must click load more like Reddit to load more comments from that comment or load more children from that comment. – Lahnda