Problem Statement
How do you traverse a hierarchy with a recursive CTE to get full paths and depths? How do you avoid cycles?
Explanation
Seed the root rows in the anchor member with level one and a path column that starts at the root. In the recursive member, join children to parents, increase the level, and append to the path. This produces a row for each reachable node with its full chain.
To avoid cycles, track visited ids in the path and stop when the next id already appears. You can also add a max depth guard to prevent runaway recursion if the data is corrupt.
Code Solution
SolutionRead Only
WITH RECURSIVE tree AS ( SELECT id, parent_id, 1 AS lvl, CAST(id AS TEXT) AS path FROM nodes WHERE parent_id IS NULL UNION ALL SELECT n.id, n.parent_id, t.lvl+1, t.path||'>'||n.id FROM nodes n JOIN tree t ON n.parent_id=t.id WHERE POSITION(CAST(n.id AS TEXT) IN t.path)=0 AND t.lvl<20 ) SELECT * FROM tree;
Practice Sets
This question appears in the following practice sets:
