Inuk
Inuk
PostsComments
PPrisma
Created by Inuk on 7/24/2024 in #help-and-questions
Recursive CTE
Hello all, I'm trying to make a recursive query, but I'm not sure where to start? I have something like the following model: 
model Role {
    id   String @unique
    name String @unique

    parent RoleRole[] @relation("parent")
    child  RoleRole[] @relation("child")
}

model RoleRole {
    parentId String
    childId  String

    parent Role @relation("parent", fields: [parentId], references: [id])
    child  Role @relation("child", fields: [childId], references: [id])

    assignedAt DateTime @default(now())
    assignedBy String

    @@id([parentId, childId])
}
model Role {
    id   String @unique
    name String @unique

    parent RoleRole[] @relation("parent")
    child  RoleRole[] @relation("child")
}

model RoleRole {
    parentId String
    childId  String

    parent Role @relation("parent", fields: [parentId], references: [id])
    child  Role @relation("child", fields: [childId], references: [id])

    assignedAt DateTime @default(now())
    assignedBy String

    @@id([parentId, childId])
}
And am looking to make a query that flattens this hierachy like the following example found on postgres' official docs: 
WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree;
WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree;
My current two ideas is to use either raw SQL queries or make views, any thoughts?
4 replies