使用PostgreSQL以简单的方式获取所有父母 [英] Fetching all parents in simple way with PostgreSQL
本文介绍了使用PostgreSQL以简单的方式获取所有父母的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个具有层次结构的表:
I have a table with a hierarchical structure:
_________
|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 $ c的所有父母$ c>,因此我可以构建一个面包屑进行导航。使用
id = 6
我想获得:
I want to list all parents of the plan name Paints
, so I can build a breadcrumb to navigate back. Using id = 6
I like to get:
Painting > Shopping > Paints
我正在将postgresql与PHP结合使用,并思考一种高效的方法来获取所有父母,就像简单
I'm using postgresql with PHP, and thinking of efficient way to fetch all the parents as simple as possible.
推荐答案
使用使用查询递归:
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)
您可以使用父ID的整数数组代替使用文本列汇总计划名称:
Instead of integer array of parent ids you can use text column to aggregate plan names:
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)
这篇关于使用PostgreSQL以简单的方式获取所有父母的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文