CODEWITHSUNDEEP
sqlpostgresqlrecursioncommon-table-expression
Pavel Murnikov
Pavel Murnikov

Reputation: 103

Postgres self-join recursive CTE ancestry chain

I have a pilates_bill table representing direct ancestry (not a tree structure)

bill_id (pk) | previous_bill_id (self-join fk)
=============+================================
1               2
2               3
3               4
5               NULL

Need to produce a list (parent / grandparent / etc) of all ancestors for any given row (below examples start with 1).

Obtaining a list of bill_ids with ancestor chain using recursive CTE

WITH RECURSIVE chain(from_id, to_id) AS (
  SELECT NULL::integer, 1  -- starting id
  UNION
  SELECT c.to_id, pilates_bill.previous_bill_id
  FROM chain c
  LEFT OUTER JOIN pilates_bill ON (pilates_bill.bill_id = to_id)
  WHERE c.to_id IS NOT NULL
)
SELECT from_id FROM chain WHERE from_id IS NOT NULL;

Result 1,2,3,4,5 as expected

But now when I try to produce table rows in order of ancestry the result is broken

SELECT * FROM pilates_bill WHERE bill_id IN
(
WITH RECURSIVE chain(from_id, to_id) AS (
  SELECT NULL::integer, 1
  UNION
  SELECT c.to_id, pilates_bill.previous_bill_id
  FROM chain c
  LEFT OUTER JOIN pilates_bill ON (pilates_bill.bill_id = to_id)
  WHERE c.to_id IS NOT NULL
)
SELECT from_id FROM chain WHERE from_id IS NOT NULL
)

Row order is 5,1,2,3,4

What a I doing wrong here ?

Upvotes: 0

Views: 964

Answers (1)

Andomar
Andomar

Reputation: 238096

The rows returned by a SQL query are in random order unless you specify an order by.

You can calculate depth by keeping track of it in the recursive CTE:

WITH RECURSIVE chain(from_id, to_id, depth) AS
    (
    SELECT  NULL::integer
    ,       1
    ,       1
    UNION
    SELECT  c.to_id
    ,       pb.previous_bill_id
    ,       depth + 1
    FROM chain c
    LEFT JOIN 
            pilates_bill pb
    ON      pb.bill_id = c.to_id
    WHERE   c.to_id IS NOT NULL
    )
SELECT  * 
FROM    chain
ORDER BY
        depth

Upvotes: 2

Related Questions