Базовый каркас WITH RECURSIVE мы освоили. Теперь — практика. В этом уроке два классических дерева: иерархия сотрудников и категории товаров. Покажем оба направления обхода, считаем глубину, материализуем путь и сделаем результат пригодным для UI.

Top-down: от корня к листьям

«Top-down» — это «сверху вниз»: anchor — корень или произвольный узел, recursive term добавляет тех, у кого parent равен уже найденным.

Это та самая схема из прошлого урока. Применим её к категориям товаров:

Получаем «Электронику» (depth=0), затем «Ноутбуки», «Смартфоны», «Аксессуары» (depth=1). В нашем seed-датасете глубже категорий пока нет — но если завтра добавить «Игровые ноутбуки» с parent_id=4, запрос сам подцепит новый уровень.

Bottom-up: от листа к корню

«Bottom-up» — это «снизу вверх»: anchor — конкретный узел в глубине, recursive term поднимается по parent_id, пока не дойдём до корня.

Это нужно, когда:

• Хочешь построить «хлебные крошки»: Электроника / Ноутбуки / MacBook Air.
• Хочешь понять, к каким верхнеуровневым категориям относится товар (для биллинга, для аналитики по департаментам).
• Хочешь узнать всех начальников сотрудника, до самого CEO.

Цепочка получается: Полина (level=0) → Елена Кравцова (Backend Lead) → Галина Сидорова (VP Eng) → Борис Морозов (CTO) → Анна Громова (CEO). Каждое звено — это parent_id предыдущего.

Обрати внимание на условие JOIN: в top-down мы пишем c.parent_id = d.id (ищем детей), в bottom-up — m.id = a.manager_id (ищем родителя). Это одна и та же таблица, но направление обхода зависит от того, какую сторону связки кладёшь в anchor.

Глубина — это просто колонка

В обоих направлениях мы накручиваем колонку depth или level. Это не часть синтаксиса CTE — это обычное поле, которое мы сами завели. Anchor ставит начальное значение (0), recursive term инкрементирует (+ 1).

С глубиной можно делать всё, что хочется в обычном SQL:

• Фильтровать (WHERE depth <= 3 — только три уровня от корня).
• Использовать как терминатор (AND depth < 100).
• Печатать с отступом.

Один колонка tree с символами-отступами — и дерево читается глазами. В UI обычно вместо REPEAT(' ') ставят CSS-padding по полю depth, но идея та же.

Материализация пути

Дальше — обычный вопрос: «А мне нужны не только все потомки, но и путь к каждому от корня». Например, для категории «Художественная литература» путь — это Книги / Художественная литература.

Решение: тащим в recursive term строку-«накопитель», в которой склеиваем имена.

Та же идея работает для путей в иерархии сотрудников, цепочек категорий в e-commerce-фильтрах, навигационных бредкрамбов в админке.

Массив path_ids теперь позволяет, например, написать WHERE 1 = ANY(path_ids) — «эта категория — потомок Электроники?».

Категория + товары: подсчёт по поддереву

Реальная задача: «сколько товаров лежит в Электронике и во всех её потомках».

Здесь CTE собирает все id «Электроника + потомки», а основной запрос фильтрует товары по этому набору. Это классическая комбинация: рекурсия наверху, обычная агрегация ниже.

Bottom-up для категории товара

Симметричная задача: по конкретному товару найти верхнюю категорию (для аналитики по департаментам).

Anchor стартует с «текущей» категории каждого товара. Recursive term поднимается, пока parent_id не станет NULL. Финальный фильтр WHERE parent_id IS NULL оставляет только корневые записи — это и есть верхняя категория.

Чек-лист урока

• Top-down: anchor — корень/произвольный узел, recursive term — JOIN child.parent_id = parent.id. Движение вниз.
• Bottom-up: anchor — лист/произвольный узел, recursive term — JOIN parent.id = child.parent_id. Движение вверх.
• Глубина (depth/level) — это обычная колонка: ставим 0 в anchor, + 1 в recursive term.
• Путь можно тащить строкой (для отображения) или массивом id (для дальнейших JOIN-ов и ANY()).
• Сочетание рекурсивного CTE и агрегации в основном запросе — типовой паттерн: «собрать поддерево, потом посчитать».