Recursive parent child query - postgresql

Question

I'm trying to prepare a recursive query that will generate data about parent-child relationships within a single table.

Here's some test data:

CREATE TABLE test
(
  id INTEGER,
  parent INTEGER
);


INSERT INTO test (id, parent) VALUES
  (2, 1),
  (3, 1),
  (7, 2),
  (8, 3),
  (9, 3),
  (10, 8),
  (11, 8);

Excepted results:
--  id |    ancestry | parent | 
--   1 |         {1} |      1 |  
--   2 |       {1,2} |      1 |       
--   3 |       {1,3} |      1 |                 
--   7 |     {1,2,7} |      2 |                 
--   8 |     {1,3,8} |      3 |                 
--   9 |     {1,3,9} |      3 |                 
--  10 |  {1,3,8,10} |      8 |                 
--  11 |  {1,3,8,11} |      8 |

#Query 1 : Return all parents for children 11 - {1,3,8,11}. The problem here is that I don't know how to mark that the first parent of 11 is 8.

WITH RECURSIVE c AS (
   SELECT 11 AS id
   UNION
   SELECT t.parent
   FROM test AS t
   JOIN c ON c.id = t.id
)
SELECT * FROM c;

Thanks in advance.

sticky bit · Accepted Answer · 2021-07-06 19:08:10Z

You can carry a counter to see the position in the hierarchy.

WITH RECURSIVE
c
AS
(
SELECT 11 AS id,
       0 AS n
UNION ALL
SELECT t.parent,
       c.n + 1
       FROM test AS t
            INNER JOIN c
                       ON c.id = t.id
)
SELECT *
       FROM c
       ORDER BY n;

8 will have 1 in your example, therefore designating it as first parent.
db<>fiddle

Edit:

Based on that you can extend your anchor set to all nodes. It's a little unfortunate that there's is no entry for 1. A quick fix is UNION ALLing a record for it as well (But you should permanently fix this by inserting a record (1, NULL) into test.). Have a third column carrying the actual leaf the hierarchy started from and GROUP BY that in the end. Use array_agg() to get the ancestry array. (Filter NULL nodes that will be in there once you inserted (1, NULL) into test.) To get the immediate parent of a leaf you can use max() (or min()) on the id filtering for the counter being 1.

WITH RECURSIVE
c
AS
(
SELECT 1 AS leaf,
       1 AS parent,
       0 AS n
UNION ALL
SELECT id AS leaf,
       id AS parent,
       0 AS n
       FROM test
UNION ALL
SELECT leaf,
       t.parent,
       c.n + 1
       FROM test AS t
            INNER JOIN c
                       ON c.parent = t.id
)
SELECT leaf AS id,
       array_agg(parent ORDER BY n DESC) FILTER (WHERE parent IS NOT NULL) AS ancestry,
       max(parent) FILTER (WHERE n = 1) AS parent
       FROM c
       GROUP BY leaf
       ORDER BY leaf;

db<>fiddle

Collectives™ on Stack Overflow

Recursive parent child query - postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related