Postgres Recursively resolve tree relation with left joins

All we need is an easy explanation of the problem, so here it is.

I have a table

| uuid | name | parent_id |

Where parent_id is nullable.

I want to write a query which given a specific row, will recursively traverse a path up until a row with null parent_id is reached and join (so I can cast it into a recursive class in the application)

So if I have

uuid name parent_id
uuid1 name1 null
uuid2 name2 uuid1
uuid3 name3 uuid2

For a query on uuid3, I want to return

uuid name parent_id b.uuid b.name b.parent_id c.uuid c.name c.parent_id
uuid3 name3 uuid2 uuid2 name2 uuid1 uuid1 name1 null

and for a query on uuid2

uuid name parent_id b.uuid b.name b.parent_id
uuid2 name2 uuid1 uuid1 name1 null

When I started reading up on recursive queries, not surprisingly I hit WITH RECURSIVE. I tried playing around with it a little bit (see some attempts at the bottom) but it seems like the restriction on the recursive query and non-recursive query makes it unfit for this purpose where the number of columns is not known in advance. It is more for cases, when you want to trace rows and return them as individual results.

Is something like this actually possible to do in Postgres? Would appreciate any hints

Thank you

P.S.: I appreciate any concerns/comments regarding the performance implications of such a query but the table is guaranteed to be very small (< 1000 rows) and the max depth of such a relation is guaranteed to be < 4 levels

I am also specifically interested if this is possible in Postgres. I am aware that graph based databases like Neo4j serve exactly these purposes.

WITH RECURSIVE rg AS (
    SELECT
        uuid, name, parent_id,
    FROM
        genres 
    WHERE uuid = '....'
    UNION
        SELECT
            g.uuid, g.name, g.parent_id, 
        FROM
            genres g
        LEFT JOIN genres b ON g.parent_id = b.uuid  
) SELECT * FROM rg;
WITH RECURSIVE rg AS (
  SELECT uuid, name, parent_id
  FROM genres
  WHERE uuid = '....'

  UNION 

  SELECT child.uuid, child.name, child.parent_id
    FROM genres AS child
    JOIN pc ON pc.uuid = child.parent_id 
)
SELECT rg.*
FROM genres rg
  JOIN b on rg.parent_id = b.uuid
;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

While not an answer to your question as stated (dynamic columns) you could use a recursive query to return columns that contain arrays of parents. Something like (adjust as needed):

WITH RECURSIVE toc AS (
    SELECT genres.uuid,
            genres.name,
            '{}'::uuid[] AS parents,
            genres.parent_id,
            1 AS path_depth,
            ARRAY [ dense_rank () OVER (
                ORDER BY genres.name ) ] AS outln,
            ARRAY[genres.uuid] AS id_path,
            ARRAY[genres.name] AS name_path
        FROM genres
        WHERE genres.parent_id IS NULL
    UNION ALL
    SELECT genres.uuid,
            genres.name,
            parents || genres.parent_id,
            genres.parent_id,
            ( q.path_depth + 1 ) AS path_depth,
            ( q.outln || dense_rank () OVER (
                PARTITION BY genres.parent_id
                ORDER BY genres.name ) ) AS outln,
            q.id_path || genres.uuid,
            q.name_path || genres.name
        FROM genres
        JOIN toc q
            ON ( genres.parent_id = q.uuid
                AND NOT genres.uuid = ANY ( q.parents ) ) -- avoid cyclic references
)
SELECT toc.uuid,
        toc.name,
        toc.parent_id,
        toc.parents,
        toc.path_depth,
        toc.id_path,
        toc.name_path,
        array_to_string ( toc.outln, '.'::text ) AS outln,
        array_to_string ( toc.id_path, ' > '::text ) AS id_path_str,
        array_to_string ( toc.name_path, ' > '::text ) AS name_path_str
    FROM toc

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply