CODEWITHSUNDEEP
sqlpostgresql
oderfla
oderfla

Reputation: 1797

Postgres query to fetch both masters and slaves

Probably bad title, sorry for that. This is the situation:

Users-table:

id
name

Connections-table:

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);

With the above inserts, Jack has Marc as slave. But he is Susie's slave too. Even Paul is Susie's slave.

Now I need to fetch all the people that are Jack's slaves. But I also need to fetch jack's masters and that master's slaves.

In english it would mean that for jack I would get all the users in the table. As Marc is Jack's slave. Susie is Jack's master. And Paul is Susie's slave (and Susie was Jack's master, so Paul is in some way in my list of users I belong too in some way).

Hope this was quite clear.

Is it possible to get all this in one query? Right now there is a query that fetches all the Jack's slaves. I built one for getting all the Jack's masters. But then I would need to loop each master to get all his slaves. All this would generate at least 3 queries. As Im using node.js with all the callback stuff it is not really a good option. I was thinking about UNION but Im not sure it is the way to go. And also stored procedure, I would prefer to avoid them.

Actually, using UNION I can select both the rows where Im slave and those where Im master. But I still don't know how to fetch those rows where my masters are master_id.

---EDIT---

I am running this query now:

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)

It seems it is giving me the expected result. Which in this case would be all the rows in the table connections. Do you think it looks correct?

Upvotes: 0

Views: 732

Answers (1)

JNevill
JNevill

Reputation: 50248

This is the type of scenario where a Recursive CTE is very helpful. A recursive CTE is a special CTE that refers back to itself. We use it for traversing hierarchies instead of making a bunch of self joins, which is generally a bad option when the depth of the hierarchy is fluid across it's different paths, and over time.

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

Upvotes: 1

Related Questions