postgresql – 使用Query递归查找父

test=> CREATE TABLE node (
test(>   id SERIAL,test(>   label TEXT NOT NULL,-- name of the node
test(>   parent_id INT,test(>   PRIMARY KEY(id)
test(> );
NOTICE:  CREATE TABLE will create implicit sequence "node_id_seq" for serial column "node.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "node_pkey" for table "node"
CREATE TABLE

…和一些数据…

test=> INSERT INTO node (label,parent_id) VALUES ('n1',NULL),('n2',1),('n3',2),('n4',3);
INSERT 0 4
test=> INSERT INTO node (label) VALUES ('garbage1'),('garbage2'),('garbage3');
INSERT 0 3
test=> INSERT INTO node (label,parent_id) VALUES ('garbage4',6);
INSERT 0 1
test=> SELECT * FROM node;
id |  label   | parent_id 
----+----------+-----------
 1 | n1       |          
 2 | n2       |         1
 3 | n3       |         2
 4 | n4       |         3
 5 | garbage1 |          
 6 | garbage2 |          
 7 | garbage3 |          
 8 | garbage4 |         6
(8 rows)

这对节点中的每个id执行递归查询：

test=> WITH RECURSIVE nodes_cte(id,label,parent_id,depth,path) AS (
 SELECT tn.id,tn.label,tn.parent_id,1::INT AS depth,tn.id::TEXT AS path 
 FROM node AS tn 
 WHERE tn.parent_id IS NULL
UNION ALL
 SELECT c.id,c.label,c.parent_id,p.depth + 1 AS depth,(p.path || '->' || c.id::TEXT) 
 FROM nodes_cte AS p,node AS c 
 WHERE c.parent_id = p.id
)
SELECT * FROM nodes_cte AS n ORDER BY n.id ASC;
id |  label   | parent_id | depth |    path    
----+----------+-----------+-------+------------
 1 | n1       |           |     1 | 1
 2 | n2       |         1 |     2 | 1->2
 3 | n3       |         2 |     3 | 1->2->3
 4 | n4       |         3 |     4 | 1->2->3->4
 5 | garbage1 |           |     1 | 5
 6 | garbage2 |           |     1 | 6
 7 | garbage3 |           |     1 | 7
 8 | garbage4 |         6 |     2 | 6->8
(8 rows)

这得到所有后代WHERE node.id = 1：

test=> WITH RECURSIVE nodes_cte(id,tn.id::TEXT AS path FROM node AS tn WHERE tn.id = 1
UNION ALL                   
 SELECT c.id,(p.path || '->' || c.id::TEXT) FROM nodes_cte AS p,node AS c WHERE c.parent_id = p.id
)                                                                
SELECT * FROM nodes_cte AS n;
id | label | parent_id | depth |    path    
----+-------+-----------+-------+------------
 1 | n1    |           |     1 | 1
 2 | n2    |         1 |     2 | 1->2
 3 | n3    |         2 |     3 | 1->2->3
 4 | n4    |         3 |     4 | 1->2->3->4
(4 rows)

以下将获取节点的ID为4的路径：

test=> WITH RECURSIVE nodes_cte(id,node AS c 
 WHERE c.parent_id = p.id
)
SELECT * FROM nodes_cte AS n WHERE n.id = 4;
id | label | parent_id | depth |    path    
----+-------+-----------+-------+------------
 4 | n4    |         3 |     4 | 1->2->3->4
(1 row)

让我们假设你想限制搜索到深度小于3的后代(请注意,深度还没有增加)：

test=> WITH RECURSIVE nodes_cte(id,path) AS (
  SELECT tn.id,tn.id::TEXT AS path 
  FROM node AS tn WHERE tn.id = 1
UNION ALL
  SELECT c.id,(p.path || '->' || c.id::TEXT) 
  FROM nodes_cte AS p,node AS c 
  WHERE c.parent_id = p.id AND p.depth < 2
)
SELECT * FROM nodes_cte AS n;
 id | label | parent_id | depth | path 
----+-------+-----------+-------+------
  1 | n1    |           |     1 | 1
  2 | n2    |         1 |     2 | 1->2
(2 rows)

我建议使用ARRAY数据类型而不是用于演示“路径”的字符串,但箭头更多地说明了父< =>子关系.