Рекурсивный запрос на postgres
Когда различные иерархии находятся в пределах одной таблицы, может потребоваться пройтись по одной из веток этой иерархии, начиная с самого нижнего элемента и заканчивая корневым. Примером иерархических данных может служить структура каталогов на файловой системе, оргструктура в компании или справочник географических объектов (города-страны-континенты).
PostgreSQL предоставляет специальный синтаксис with recursive для написания рекурсивных запросов. Эти запросы позволяют легко выбирать иерархические данные.
Данный гайд также доступен в формате видео на YouTube.
Рассмотрим конкретную таблицу:
create table hierarchy_example (
id serial not null ,
name character varying ( 100 ),
parent_id integer ,
constraint id_pk primary key (id)
)
Здесь поле parent_id содержит номер записи, которая является родительской по отношению к данной. Если parent_id = null, считаем, что это – корневой элемент иерархии.
insert into hierarchy_example (name, parent_id) values ( ‘root’ , null );
insert into hierarchy_example (name, parent_id) values ( ‘item1’ , 1 );
insert into hierarchy_example (name, parent_id) values ( ‘item2’ , 1 );
insert into hierarchy_example (name, parent_id) values ( ‘subitem1’ , 2 );
Теперь составим запрос для прохода по этой иерархии, от элемента с именем subitem1 до root. На каждой итерации будем добавлять новую строку во временную таблицу temp1.
with recursive temp1 (id, parent_id, name, path) as (
select t1.id, t1.parent_id, t1.name, cast (t1.name as varchar ( 50 )) as path
from hierarchy_example t1 where t1.name = ‘subitem1’
union
select t2.id, t2.parent_id, t2.name, cast (temp1.path || ‘->’ || t2.name as varchar ( 50 ))
from hierarchy_example t2 inner join temp1 on (temp1.parent_id = t2.id))
select * from temp1
Рекурсивный запрос начинается с ключевых слов with recursive. Далее следует именованный набор полей временной таблицы temp1, в которую мы будем добавлять данные на каждой итерации.
Внутри рекурсивный запрос (то, что записано в скобках после ключевого слова as) можно разделить на две части, которые объединены ключевым словом union. Первая часть – это запрос для поиска элемента, с которого следует начать рекурсивный запрос. Вторая часть – то, что выполняется в каждой итерации. Здесь мы выбираем номер элемента, номер его родительского элемента, а также для наглядности определяем временную переменную path, в которой будет содержаться пройденный путь по иерархии.
В самом конце следует обычный запрос, который выполняется к временной таблице temp1, в которую мы помещали строки в каждой итерации.
Результат выполнения запроса:
| id | parent_id | name | path |
| 4 | 2 | subitem1 | subitem1 |
| 2 | 1 | item1 | subitem1->item1 |
| 1 | root | subitem1->item1->root |
Как видим, в столбце path последовательно отображается путь от subitem1 до root. Также обратите внимание, что в столбце name нет элемента item2 – он не входит в данную ветвь иерархии.
Источник