CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expression
nightfly
nightfly

Reputation: 455

simple common table expression to flatten a tree

Following is the table format that I have. Table name :: USERS

userid reporttouserid
------ ------------
101    NULL
102    101
103    102

Now I need a query to list all the child user ids under 101 that is 102 and 103 both (103 is indirectly under 101 as its parent 102 is under 101)

I have seen the common table expression in postgresql but not being able to figure out how to go about it.

Upvotes: 1

Views: 1655

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 325141

The PostgreSQL documentation covers this topic. See the examples given for recursive CTEs on that page.

Recursive CTEs can be a bit hard to grasp, but are very powerful once you use them. Read the documentation and experiment a little; you'll get it.

(Please always mention your PostgreSQL version and show desired output in table-like form in your questions).

Given demo data:

create table users (
  userid integer primary key,
  reporttouserid integer references users(userid)
);

insert into users(userid, reporttouserid) values (101,null), (102,101), (103,102);

(please provide this in questions where possible, it's a pain to have to create it)

you can recursively walk the graph with something like:

WITH RECURSIVE flatusers(userid, reporttouserid, baseuserid) AS (
    SELECT userid, reporttouserid, userid AS baseuserid 
    FROM users WHERE reporttouserid IS NULL
    UNION ALL
    SELECT u.userid, u.reporttouserid, f.baseuserid
    FROM flatusers f
    INNER JOIN users u ON f.userid = u.reporttouserid

)
SELECT * FROM flatusers;

producing output like:

 userid | reporttouserid | baseuserid 
--------+----------------+------------
    101 |                |        101
    102 |            101 |        101
    103 |            102 |        101
(3 rows)

I'm sure you can figure out where to go from there. Make sure you understand that recursive CTE before using it.

Be aware that PostgreSQL (9.4 or older at least) cannot (unfortunately) push quals down into CTE terms, even for non-recursive CTEs. If you add WHERE baseuserid = 101 to your query, the query will still generate the entire flattened table, then throw most of it away. If you want to do this recursive operation for just one baseuserid, you must add the appropriate WHERE clause term after WHERE reporttouserid IS NULL in the static union part of the recursive CTE term.

Upvotes: 6

Related Questions