Code Snippets — 2026-07-04

🗃️ Code Snippet — July 4, 2026

Recursive CTE for Hierarchical Data with Depth, Path & Ancestor Tracking (SQL)

Use when you have flat parent-child data: org charts, category trees, comment threads, file structures, bill-of-materials. Walks the entire tree in one query — no N+1 problem.

WITH RECURSIVE hierarchy AS (
    -- Anchor: root nodes
    SELECT id, name, parent_id,
           0 AS depth,
           CAST(id AS TEXT) AS path,
           '' AS ancestors,
           TRUE AS is_root
    FROM categories WHERE parent_id IS NULL

    UNION ALL

    -- Recursive: children
    SELECT c.id, c.name, c.parent_id,
           h.depth + 1 AS depth,
           h.path || ',' || CAST(c.id AS TEXT) AS path,
           CASE WHEN h.ancestors = ''
                THEN CAST(h.id AS TEXT)
                ELSE h.ancestors || ',' || CAST(h.id AS TEXT)
           END AS ancestors,
           FALSE AS is_root
    FROM categories c
    INNER JOIN hierarchy h ON c.parent_id = h.id
    WHERE h.depth < 50  -- Safety: prevent infinite loops
)
SELECT id, name, depth, path, ancestors, is_root,
       LPAD('  ', depth * 2) || '└─ ' || name AS tree_view
FROM hierarchy ORDER BY path;

Practical queries:

• All descendants of node 5: WHERE path LIKE '5,%' OR id = 5


• Count per level: SELECT depth, COUNT() FROM hierarchy GROUP BY depth


• Leaf nodes only: WHERE NOT EXISTS (SELECT 1 FROM categories c WHERE c.parent_id = h.id)


• Limit depth: WHERE depth <= 3

⚠️ Works on PostgreSQL, MySQL 8.0+, SQLite 3.34+, SQL Server, Oracle — ANSI SQL:1999 standard. For PostgreSQL, use ARRAY_APPEND instead of string concatenation for paths.

💡 Bookmark this — every app with trees needs recursive queries.*