A recursive CTE walks a tree without a loop in PHP

Fetching a category and all its descendants meant a query per level, in a loop, with the depth unknown — so the number of round trips depended on the data.

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          -- the guard that must be there
)
SELECT * FROM tree;

The depth guard is not optional: a cycle in the data — which happens the first time somebody sets a category as its own ancestor through the admin — recurses until cte_max_recursion_depth stops it with an error, and that default is 1,000. Adding an explicit limit turns an error into a bounded result. One query instead of eleven is the win, and it stays one query as the tree grows.