Рекурсивный CTE позволяет обходить иерархию, когда строка ссылается на родителя через parent_id. В PostgreSQL запрос начинается с WITH RECURSIVE, выбирает начальные узлы, а затем многократно присоединяет их потомков. Для данных с возможными циклами нужно явно определить, когда прекращать повторный обход.
В упражнении Mentoring.digital получим дерево учебных категорий. Пример рассчитан именно на PostgreSQL: запись массивов и проверка ANY не переносятся в SQL Server или MySQL без изменений.
Разделите начало обхода и следующий шаг
WITH RECURSIVE
categories(id, parent_id, name) AS (
VALUES (1, NULL::integer, 'Courses'),
(2, 1, 'Development'),
(3, 2, 'Python'),
(4, 1, 'Design')
),
tree AS (
SELECT id, parent_id, name, 0 AS depth, ARRAY[id] AS path
FROM categories WHERE id = 1
UNION ALL
SELECT c.id, c.parent_id, c.name, t.depth + 1, t.path || c.id
FROM categories c
JOIN tree t ON c.parent_id = t.id
WHERE NOT c.id = ANY(t.path)
)
SELECT id, name, depth, path
FROM tree
ORDER BY path;Запрос содержит данные внутри VALUES и не создаёт таблицу. Начальная часть tree выбирает категорию 1, ставит глубину 0 и начинает путь с её id. Рекурсивная часть ищет строки, чей parent_id совпадает с текущим id. Новая строка получает увеличенную глубину и дополненный путь.
При указанных данных порядок id равен 1, 2, 3, 4, глубины равны 0, 1, 2, 1. Это результат явного ORDER BY path. Сам порядок вычисления рекурсивных шагов не нужно использовать как обещание порядка итоговой выдачи.
Почему UNION ALL не является защитой от цикла
UNION ALL сохраняет строки каждого шага, включая повторы. Если цепочка родителей снова приводит к уже посещённому узлу, без дополнительного условия обход может продолжаться. Простая замена на UNION тоже не универсальна: при изменяющихся depth или path строки уже не совпадают целиком.
Условие NOT c.id = ANY(t.path) проверяет, отсутствует ли следующий id в пути именно этой ветви обхода. Это защита пути, а не глобальное удаление всех повторений результата. В графе с несколькими маршрутами один узел может встречаться через разные пути, и это нужно согласовать с назначением запроса.
Выберите корень и поведение для повреждённых данных
Для всего леса категорий начальную часть можно заменить на WHERE parent_id IS NULL. Но узлы без доступного родителя и замкнутые циклы без корня тогда могут не попасть в результат. Отсутствие строки в дереве не доказывает отсутствие этой строки в исходных данных.
Для реальной таблицы задайте уникальность id, допустимость parent_id и правила удаления родителей. Внешний ключ помогает обнаруживать ссылки на отсутствующие строки, но сам по себе не запрещает циклы. Ограничение глубины также может пригодиться, однако его достижение должно быть заметным: молча обрезанное дерево нельзя выдавать за полный результат.
Как проверить обход перед работой с большой таблицей
- Проверьте одиночный корень и корень с несколькими детьми.
- Убедитесь, что выбранный отсутствующий id даёт пустой результат.
- В учебных VALUES измените parent_id первой строки на 3. Цепочка 1, 2, 3 снова приведёт к 1, и условие пути должно остановить повторный заход.
- Проверьте отдельную ветвь, недостижимую от выбранного корня, и определите, нужна ли она в отчёте.
На рабочей таблице важен индекс по parent_id для поиска детей, но конкретный эффект проверяют планом запроса на подходящих данных. Накопление длинных путей и ветвление требуют памяти и времени; небольшой пример не является замером производительности вашей базы.
На курсе SQL с наставником этот запрос можно связать с деревом разделов или организационной структурой. Для разбора с наставником по SQL подготовьте схему таблицы, несколько обезличенных строк и ожидаемый порядок результата.
