Рекурсивные CTE в SQL: иерархии и графы

Из двух запросов, соединённых UNION ALL внутри WITH RECURSIVE name AS (...). Первый — якорь (anchor): стартовое множество строк, например корень дерева WHERE manager_id IS NULL или конкретный сотрудник. Второй — рекурсивная часть, которая делает JOIN самого CTE с исходной таблицей и добавляет следующий уровень: FROM employees e JOIN name n ON e.manager_id = n.id. PostgreSQL повторяет рекурсивную часть, подставляя результат предыдущей итерации, пока она не вернёт пустой набор.

Якорь — сам сотрудник: SELECT id, manager_id, name, 1 AS lvl FROM employees WHERE id = 42. Рекурсия поднимается вверх: SELECT e.id, e.manager_id, e.name, c.lvl + 1 FROM employees e JOIN chain c ON e.id = c.manager_id. На каждом шаге по manager_id находим начальника текущего уровня, счётчик lvl растёт. Итог — вся вертикаль от сотрудника до генерального директора. Если нужно наоборот, разматывать вниз всех подчинённых, меняете условие JOIN на e.manager_id = c.id.

Два способа. Первый, портативный: тащите массив пройденных id через path = path || e.id и в рекурсивной части добавляете условие WHERE NOT e.id = ANY(c.path), чтобы не заходить в уже посещённый узел. Второй, специфичный для PostgreSQL от версии 14: клауза CYCLE id SET is_cycle USING path, которая сама детектит циклы и помечает их. Без защиты грязные данные (менеджер сам себе начальник или взаимные ссылки) отправят запрос в бесконечный цикл.

Обычный JOIN работает только на фиксированную известную глубину: три JOIN — три уровня. Если завтра в дереве появится пятый уровень, запрос молча потеряет данные. Рекурсивный CTE обрабатывает произвольную глубину одним запросом, потому что сам решает, сколько итераций нужно, пока строки не кончатся. Плюс он естественно накапливает путь и уровень вложенности. На собесе именно за этот аргумент про переменную глубину дают плюс — кандидат понимает, почему JOIN тут принципиально не подходит.

Ведёте два накопителя в рекурсии. Уровень — обычный счётчик: lvl = c.lvl + 1 от якоря с lvl = 1. Путь — строковая или массивная конкатенация: path = c.path || ' > ' || cat.name либо path_ids = c.path_ids || cat.id. Тогда для каждой категории вы одновременно получаете её глубину и хлебные крошки вроде 'Электроника > Ноутбуки > Игровые'. Это ровно тот результат, который просят для навигации каталога или breadcrumbs на сайте.