myles
DTDrizzle Team
•Created by myles on 11/12/2023 in #help
WITH RECURSIVE support… or suggestions for how to approach?
Hi. I'm absolutely loving Drizzle after a little while in both Prisma and TypeORM and 15 years of Rails + ActiveRecord.
I have the following query that uses WITH RECURSIVE. I'd love to use Drizzle's query builder but I cant see how I might tackle that. Am I right in thinking that this is going to need to be supported in the
select helper https://orm.drizzle.team/docs/select#with-clause ?
```
WITH RECURSIVE layer AS (
SELECT
id,
name,
parent_id,
0 AS level
FROM
risks
WHERE
parent_id IS NULL
UNION ALL
SELECT
child.id,
child.name,
child.parent_id,
level + 1 AS level
FROM
risks child
JOIN layer l ON l.id = child.parent_id
)
SELECT
l.*,
parent.id AS parent_id
FROM
layer l
LEFT JOIN risks parent ON l.parent_id = parent.id
ORDER BY
level;5 replies