Walk a category tree with a recursive CTE

SnippetMay 25, 2018

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 0 AS depth FROM categories WHERE id = 12
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
  WHERE t.depth < 10
)
SELECT * FROM tree ORDER BY depth, name;