Обход иерархии

Часть раздела Продвинутый уровень путешествия по SQL на Coddy — урок 21 из 27.

Настоящая причина существования рекурсивных CTE: обход иерархических отношений «родитель-потомок», хранящихся в одной таблице. Предположим, что в таблице employees есть столбец manager_id, который ссылается на ту же таблицу:

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees WHERE id = 7              -- anchor: начальный сотрудник
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON e.id = c.manager_id      -- recursive: переход к менеджеру
)
SELECT * FROM chain

На каждом шаге CTE соединяется обратно с employees, поднимаясь на один уровень вверх по дереву. Рекурсия прекращается естественным образом, когда у строки нет менеджера (JOIN ничего не находит).

Задание

Средне

Доступные таблицы и столбцы:
categories: id, name, parent_id

Каждая категория имеет необязательный parent_id, указывающий на другую категорию. Верните все категории, которые являются потомками категории id = 1 (ее детей, внуков, …), но не саму категорию 1. Верните id и name, отсортированные по id.

Шпаргалка

Используйте рекурсивные CTE для обхода отношений родитель/потомок, хранящихся в одной таблице (например, столбец manager_id или parent_id, указывающий на ту же таблицу):

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees WHERE id = 7          -- якорь: начальная строка
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON e.id = c.manager_id  -- рекурсия: переход к родителю
)
SELECT * FROM chain

Рекурсия естественным образом прекращается, когда JOIN не находит подходящих строк (например, строка без менеджера).

Попробуйте сами

WITH RECURSIVE descendants AS (
    -- anchor: прямые потомки id 1
    -- recursive: их потомки и т. д.
)
SELECT id, name FROM descendants ORDER BY id