PostegrSQL with recursive query to get ordred parent-child pathname for project-CodePudding

As you will see I have a table with project parent and child.I wanted to write a script that orders the projects by returning its pathname (not the pathID ) by using the project's parent-child ID's, I couldn't find this special case here, so I apologize if I missed it and would please you to provide me the link to the solution in the comments.

I have a table which looks like this:

| projectID |  name  | parentID | description |
| 1         |project1|      2   |description1 |
| 2         |project2|      4   |description2 |
| 3         |project3|      1   |description3 |
| 4         |project4|    NUll  |description4 |
| 5         |project5|      1   |description5 |

this is the result that i'm looking for :

| projectID |  name  | parentID | description |          Path
| 1         |project1|      2   |description1 |      project2/project1
| 2         |project2|      3   |description2 |      project3/project2/project1
| 3         |project3|      4   |description3 |      project4/project3/project2/project1
| 4         |project4|    NUll  |description4 |      project4
| 5         |project5|    NULL  |description5 |      projet5

i tried to do this :

    WITH RECURSIVE tree_view AS (
    SELECT id,
         parent_id,
         "name",
         0 AS level,
         CAST(id AS varchar(50)) AS order_sequence
    FROM  projects
    WHERE parent_id IS NULL     
UNION ALL
    SELECT parent.id,
         parent.parent_id,
         parent."name",
         level   1 AS level,
         CAST(order_sequence || '_' || CAST(parent.id AS VARCHAR (50)) AS VARCHAR(50)) AS order_sequence
    FROM projects parent
    JOIN tree_view tv
      ON parent.parent_id = tv.id
) 
SELECT
   RIGHT('------------',level*3) || "name"
     AS parent_child_tree, 
     id ,
    parent_id
FROM tree_view
ORDER BY order_sequence;

the result of this CTE shows the generational levels and who is who in this family tree and this is not what I'm searching for. I guess that I will concatenate projects name according to parentID but i have no idea how .

THANKS IN ADVANCE FOR YOUR HELP .

CodePudding user response：

Forming the path string is just adding the child name to the parent name within the recursion

WITH RECURSIVE tree_view
    AS (
        SELECT id
            , parentid
            , "name"
            , 0 AS LEVEL
            , CAST(id AS VARCHAR(50)) AS order_sequence
            , name::TEXT AS path
        FROM projects
        WHERE parentid IS NULL
        
        UNION ALL
        
        SELECT parent.id
            , parent.parentid
            , parent."name"
            , LEVEL   1 AS LEVEL
            , CAST(order_sequence || '_' || CAST(parent.id AS VARCHAR(50)) AS VARCHAR(50)) AS order_sequence
            , tv.path || '/' || parent.name
        FROM projects parent
        JOIN tree_view tv ON parent.parentid = tv.id
        )
    SELECT RIGHT('------------', LEVEL * 3) || "name" AS parent_child_tree
        , id
        , parentid
        , path
    FROM tree_view
    ORDER BY order_sequence;

example result (givenpath is as given in the sample data)

 ------------------- ---- ---------- ------------------------------------- ------------------------------------- 
| parent_child_tree | id | parentid |                path                 |              givenpath              |
 ------------------- ---- ---------- ------------------------------------- ------------------------------------- 
| project4          |  4 |          | project4                            | project4                            |
| ---project3       |  3 |        4 | project4/project3                   | project4/project3/project2/project1 |
| ------project2    |  2 |        3 | project4/project3/project2          | project3/project2/project1          |
| ---------project1 |  1 |        2 | project4/project3/project2/project1 | project2/project1                   |
| project5          |  5 |          | project5                            | projet5                             |
 ------------------- ---- ---------- ------------------------------------- -------------------------------------

db<>fiddle here