Postgres查询获取主人和奴隶

Question

可能是坏标题，对不起。 情况是这样的：

用户表：

id 
name 

联系表：

id 
master_id 
slave_id 


insert into Users values(1,'Jack'); 
insert into Users values(2,'Marc'); 
insert into Users values(3,'Susie'); 
insert into Users values(4,'Paul'); 

insert into Connections values(1,1,2); 
insert into Connections values(2,3,1); 
insert into Connections values(3,3,4); 

利用上述的插入，杰克有马克作为从站。 但他也是苏茜的奴隶。甚至保罗是苏茜的奴隶。

现在我需要抓取所有杰克奴隶的人。但我也需要取杰克的主人和那个主人的奴隶。

英语那岂不是杰克，我会得到表中的所有用户。由于马克是杰克的奴隶。苏茜是杰克的主人。 保罗是苏茜的奴隶（苏茜是杰克的主人，所以保罗在某种程度上属于我以某种方式属于我的用户列表）。

希望这很清楚。

是否有可能在一个查询中得到所有这一切？ 现在有一个查询取得所有杰克的奴隶。我建立了一个让所有杰克的主人。但是，我需要循环每个主人以获得他的所有奴隶。所有这些都会生成至少3个查询。 由于我使用node.js和所有的回调函数，它并不是一个好的选择。 我在想联盟，但林不知道这是要走的路。而且还有存储过程，我宁愿避免它们。

其实用UNION我可以同时选择的行，其中林从和那些林主。但我仍然不知道如何获取主人master_id所在的行。

---编辑---

我现在运行此查询：

select 
"connections"."master_id" 
,"connections"."slave_id" 
from 
"connections" 
where 
"connections"."master_id" = 1 
union 
select 
"connections"."master_id" 
,"connections"."slave_id" 
from 
"connections" 
where 
"connections"."slave_id" = 1  
union 
select 
"connections"."master_id" 
,"connections"."slave_id" 
from 
"connections" 
where 
"connections"."master_id" IN 
(select "connections"."master_id" from "connections" where "connections"."slave_id" = 1)   

看来它给我预期的结果。 在这种情况下，它将是表连接中的所有行。 你认为它看起来正确吗？

Answer 1

这是递归CTE非常有用的场景类型。递归CTE是一个特殊的CTE，它指向自身。我们使用它来遍历层次结构，而不是进行大量的自连接，当层次结构的深度在不同路径上流动并且随着时间流逝时，这通常是不好的选择。

WITH RECURSIVE recCTE() AS 
(
    /*Recursive Seed - The start of the recursive lookup*/ 
    SELECT 
     master_id as parent, 
     slave_id as child, 
     /*You can use "depth" to check how deep we are in the master/slave hierarchy*/ 
     1 as Depth, 
     /*You can use a "path" to see which people/nodes are involved in the hierarchy as it's built through the iterations of the recursive CTE*/ 
     CAST(master_id || '>' || child as VARCHAR(50)) as path 
    FROM 
     Connections 
    WHERE 
     /* here we determine who we are starting with for the lookup. You could start with everyone by omitting this*/ 
     /* We'll start with Susie */ 
     master_id = 3 


    /* 
     Recursive Term - The part of the query that refers to itself and iterates until 
     the inner join fails 
    */ 
    SELECT 
     recCTE.child as parent, 
     connections.slave_id as child, 
     recCTE.depth + 1 as depth, 
     recCTE.path || '>' || connections.slave_id as path 
    FROM 
     recCTE /*referred to itself here*/ 
     INNER JOIN connections ON 
      recCTE.child = connections.master_id /*Join child to master for next lookup of slave/child */ 
    WHERE 
     /*safe guard in case of endless cycling (A reporting to B reporting to C reporting back to A)*/ 
     recCTE.Depth < 15 

     /*besides checking for depth, you could also insure that the slave doesn't exist in the path already*/ 
     recCTE.path NOT LIKE '%' || slave_id || '%' 


) 

/*Now select from it and see what you get*/ 
SELECT * FROM recCTE; 

Check out the official Postgres documentation on Recursive CTEs here