CTE + рекурсия: когда нужно «пройтись» по иерархии данных

Есть задачи, где данных недостаточно «плоских» таблиц: нужно подняться по иерархии (от подчинённого к руководителю), спуститься по дереву категорий или пройти по цепочке транзакций. Тут выручают рекурсивные CTE — мощная штука, про которую многие вспоминают только тогда, когда обычный JOIN уже не спасает.

Конструкция простая по форме, но очень эффективная: WITH RECURSIVE имя_cte AS ( -- Базовый случай (с чего начинаем) SELECT ... UNION ALL -- Рекурсивный шаг (как переходим дальше) SELECT ... FROM имя_cte JOIN ... ) SELECT * FROM имя_cte;

Есть типичный формат иерархической таблицы, где фиксируется сам объект и его родительский id. К примеру давайте посмотрим на категории товаров, когда например для товара есть целый путь категорий ("Для мужчин" - "Верхняя одежда" - "Зима" - "Куртки"). В итоге с помощью рекурсии для каждого объекта можно сразу достать весь путь его родительских категорий).

WITH RECURSIVE category_path AS ( SELECT category_id, parent_category_id, name, name AS full_path, 1 AS depth FROM categories WHERE parent_category_id IS NULL -- корневые категории

UNION ALL

SELECT c.category_id, c.parent_category_id, c.name, cp.full_path || ' > ' || c.name, cp.depth + 1 FROM category_path cp JOIN categories c ON c.parent_category_id = cp.category_id ) SELECT category_id, full_path, depth FROM category_path;

Другими словами, рекурсия с cte - это некая реализация цикла с помощью SQL. Ставь 👍 если узнал новое