Prisma•5mo ago

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?

Solution:

I'm not sure I'm too satisfied with the answer, but I ended up writing a view to solve my issue, and then use seed.ts to ensure that it exists.

Jump to solution

1 Reply

Solution

Inuk•5mo ago

I'm not sure I'm too satisfied with the answer, but I ended up writing a view to solve my issue, and then use seed.ts to ensure that it exists.

Gaming

Programming

Recursive CTE