Working with Self-Relations

model Category {
  id       Int        @id @default(autoincrement())
  name     String
  parentId Int?
  parent   Category?  @relation("CategoryHierarchy", fields:[parentId], references:[id])
  children Category[] @relation("CategoryHierarchy")
}

model User {
  id          Int   @id @default(autoincrement())
  mentorId    Int?  @unique
  mentor      User? @relation("Mentorship", fields:[mentorId], references:[id])
  mentee      User? @relation("Mentorship")
}

model User {
  id        Int    @id @default(autoincrement())
  followers User[] @relation("Followers")
  following User[] @relation("Followers")
}

const root = await prisma.category.findUnique({
  where: { id: 1 },
  include: { children: { include: { children: true } } }
});

await prisma.category.create({
  data: {
    name: "Sub",
    parent: { connect: { id: parentId } }
  }
});

const tree = await prisma.$queryRaw`
  WITH RECURSIVE t AS (
    SELECT id, name, "parentId" FROM "Category" WHERE id = ${rootId}
    UNION ALL
    SELECT c.id, c.name, c."parentId" FROM "Category" c JOIN t ON c."parentId" = t.id
  ) SELECT * FROM t`;