在PostgreSQL中以简单的方式获取所有父节点 - Fetching all parents in simple way with PostgreSQL

Fetching all parents in simple way with 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的所有父项，这样我就可以构建一个导航返回的breadcrumb。使用id = 6，我喜欢得到:

Painting > Shopping > Paints

我正在使用postgresql与PHP，并考虑有效的方式来获取所有的父母尽可能简单。

使用递归查询:

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

可以使用文本列来聚合计划名称，而不是父id的整数数组:

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)