Trying get WITH RECURSIVE working in PostgreSQL-CodePudding

I'm trying to follow the examples in this article: https://learnsql.com/blog/how-to-query-hierarchical-data

I've constructed a simple table in PostgreSQL to hold hierarchical data:

media_set_id (uuid)
name (citext)
parent_id (uuid and NULL allowed)

This is the query I constructed:

WITH RECURSIVE hierarchy AS (
  SELECT media_set_id AS id,
         name,
         parent_id,
         CAST (name AS citext) AS path
  FROM media_sets
  WHERE parent_id IS NULL
  
  UNION ALL
     
  SELECT media_sets.media_set_id,
         media_sets.name,
         media_sets.parent_id,
         hierarchy.path || '\' || media_sets.name
  FROM media_sets, hierarchy
  WHERE media_sets.parent_id = hierarchy.id
)

SELECT * FROM hierarchy;

The table is populated with some sample data.

When I run the query, I get this error: relation "hierarchy" does not exist

I can't figure out what's wrong with my SQL code. Any ideas?

Robert

CodePudding user response：

Rewriting your code a little you get the result you intended.

CREATE tABLE media_sets (
media_set_id uuid,
name text,
parent_id uuid )

WITH RECURSIVE hierarchy AS (
  SELECT media_set_id AS id,
         name,
         parent_id,
         CAST (name AS text) AS path
  FROM media_sets
  WHERE parent_id IS NULL
  
  UNION ALL
     
  SELECT media_sets.media_set_id,
         media_sets.name,
         media_sets.parent_id,
         hierarchy.path || '\' || media_sets.name
  FROM media_sets JOIN hierarchy
  ON media_sets.parent_id = hierarchy.id
)

SELECT * FROM hierarchy;

id | name | parent_id | path
:- | :--- | :-------- | :---

db<>fiddle here

CodePudding user response：

Hmmm ... this alternate approach does work:

WITH RECURSIVE hierarchy AS (
  SELECT media_set_id,
         name,
         parent_id
  FROM media_sets
  WHERE parent_id IS NULL
  
  UNION ALL
     
  SELECT ms.media_set_id,
         ms.name,
         ms.parent_id
  FROM media_sets ms
  INNER JOIN hierarchy h on ms.parent_id = h.media_set_id
)
SELECT * 
FROM hierarchy;

I'm not really sure why this works and the other does not but I can work with this version as it's returning the records in the order I need to construct a hierarchical tree in the UI.

CodePudding user response：

Actually, one thing it does not do is sort the data in the way I ideally need. What I was hoping for was for the hierarchy to be returned like this:

root
parent1
child1_of_parent1
child2_of_parent1
parent2
child1_of_parent2
child1_of_child1_of_parent2
child2_of_child1_of_parent2
child2_of_parent2

If anyone knows how to force such an order of records to be returned, that's the last piece of the puzzle I'm looking to solve.

CodePudding user response：

I apologize for really flubbing up this Q&A. But out of completeness I feel it appropriate to provide a final set of SQL code which I know works, as it may help someone nbk for the assist!

WITH RECURSIVE hierarchy AS (
  SELECT media_set_id,
         name,
         parent_id,
         cast (name as text) as path
  FROM media_sets
  WHERE parent_id IS NULL
  UNION ALL
  SELECT ms.media_set_id,
         ms.name,
         ms.parent_id,
         h.path || '\' || ms.name
  FROM media_sets ms
  INNER JOIN hierarchy h on ms.parent_id = h.media_set_id
)
SELECT * 
FROM hierarchy
ORDER BY path;

The final ORDER BY returns the records in a way that will allow me to build a hierarchical UI and populate the tree from top to bottom.