Рекурсивный запрос
Редактировал(а) Alexandr Fokin 2023/12/16 14:12
Запрос задействует механизм CTE.
WITH RECURSIVE tree (nm, id, level, pathstr)
AS
(
--Первый элемент в выборке. Начало рекурсии
SELECT
nm,
id,
0,
cast('' as text)
FROM tree_sample
WHERE id_parent is null
UNION ALL
--Каждый последующий элемент рекурсии
SELECT
tree_sample.nm,
tree_sample.id,
t.level + 1,
tree.pathstr + tree_sample.nm
FROM tree_sample
INNER JOIN tree
on tree.id = tree_sample.id_parent
)
SELECT
id,
space(level) + nm as nm
FROM tree
ORDER BY pathstr
AS
(
--Первый элемент в выборке. Начало рекурсии
SELECT
nm,
id,
0,
cast('' as text)
FROM tree_sample
WHERE id_parent is null
UNION ALL
--Каждый последующий элемент рекурсии
SELECT
tree_sample.nm,
tree_sample.id,
t.level + 1,
tree.pathstr + tree_sample.nm
FROM tree_sample
INNER JOIN tree
on tree.id = tree_sample.id_parent
)
SELECT
id,
space(level) + nm as nm
FROM tree
ORDER BY pathstr
Рекурсивные SQL запросы
https://habr.com/ru/post/27439/
Дополнение:
В некоторых случаях рекурсивного запроса можно избежать (в случае извлечения из хранилища некоторого агрегата).
Достаточно помимо ключа/отношения Parent-Child, добавить ключ/отношение Root-Child.
Таким образом можно извлечь все поддерево Root элемента за 1 join.
Это немного усложнит модель - в случае выполнения update нужно не забыть обновить оба поля. (Чтобы не допустить состояния, когда родительский элемент принадлежит к другому Root элементу)