Простой выбор всех родителей с PostgreSQL

Question

Простой выбор всех родителей с PostgreSQL

У меня есть таблица с иерархической структурой:

    _________
|Plans   |_____________________________________________
|-------------------------------------------------------|
| id     | parent | plan_name      | description        |
|-------------------------------------------------------|
| 1        0        Painting        bla..bla            |
| 2        1        Shopping        bla..bla            |
| 3        1        Scheduling      bla..bla            |
| 4        2        Costumes        bla..bla            |
| 5        2        Tools           bla..bla            |
| 6        2        Paints          bla..bla            |
|_______________________________________________________|

Я хочу перечислить всех родителей имени плана PaintsТак что я могу построить крошку, чтобы вернуться назад. С помощью id = 6 Мне нравится получать:

Painting > Shopping > Paints

Я использую Postgresql с PHP, и думаю, эффективный способ получить все родители как можно проще.

1

database php postgresql recursive-query sql

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

использование рекурсивный с запросом:

with recursive pl(id, parent, parents) as (
select id, parent, array[parent]
from plans
union
select pl.id, plans.parent, pl.parents|| plans.parent
from pl
join plans on pl.parent = plans.id
)
select distinct on (id) id, parents
from pl
order by id, array_length(parents, 1) desc

id | parents
----+---------
1 | {0}
2 | {1,0}
3 | {1,0}
4 | {2,1,0}
5 | {2,1,0}
6 | {2,1,0}
(6 rows)

SqlFiddle

Вместо целочисленного массива родительских идентификаторов вы можете использовать текстовый столбец для объединения имен планов:

with recursive pl(id, parent, parents, depth) as (
select id, parent, plan_name, 0
from plans
union
select pl.id, plans.parent, plans.plan_name|| ' > ' ||pl.parents, depth+ 1
from pl
join plans on pl.parent = plans.id
)
select distinct on (id) id, parents
from pl
order by id, depth desc;

id |            parents
----+--------------------------------
1 | Painting
2 | Painting > Shopping
3 | Painting > Scheduling
4 | Painting > Shopping > Costumes
5 | Painting > Shopping > Tools
6 | Painting > Shopping > Paints
(6 rows)

2