WITH RECURSIVE

Örnek

Elimizde şöyle bir tablo olsunn

-------------------------------------------------
|  id  | description         | parent_id   |  cost
--------------------------------------------------
| 1    |  Radiology         |       NULL  | 0.00
| 2    |  Lab Tests         |       NULL  | 0.00
| 3    |  Normal Radiology  |         1   | 0.00
| 4    |  Resonance         |         1   | 100.00
| 1100 |  Cerebral Resonance|         4   | 200.00
| 1900 |  Blood Tests       |         2   | 10.00
| 2044 |  Calcium           |         2   | 50.00

---------------------------------------------------

Çıktı olarak şunu isteyelim

Radiology
   -->Normal Radiology
   -->Resonance
      -->Cerebral Resonance with contrast
Lab Test
    --> Blood Test
    --> Calcium

Şöyle yaparız

WITH RECURSIVE hierarchy AS (
    SELECT  id, 1 AS rown, CAST(description AS TEXT) AS parent_list, id as parent
    FROM    orders
    WHERE   parent_id is null
    UNION  
    SELECT  c.id
    ,rown + 1 as rown
    ,CAST(repeat('    ', rown) || ' --> ' || c.description as text) as parent_list
    ,parent
    FROM orders c
    INNER JOIN hierarchy c2 ON CAST(c.parent_id AS INTEGER) = c2.id )
SELECT id,parent_list FROM hierarchy
ORDER BY parent DESC,rown

Çıktı olarak şunu alırız

  id | parent_list                    
---: | :------------------------------
   2 | Lab Tests                      
1900 |      --> Blood Tests           
2044 |      --> Calcium               
   1 | Radiology                      
   4 |      --> Resonance             
   3 |      --> Normal Radiology      
1100 |          --> Cerebral Resonance

Şöyle yaparız

WITH RECURSIVE hierarchy AS (
    SELECT  id, CAST(description AS TEXT) AS parent_list, 1 AS rown, id as parent
    FROM    orders
    WHERE   parent_id is null
    UNION  
    SELECT  c.id
    ,CAST(c2.parent_list || ' --> ' || c.description as text) as parent_list
    ,rown + 1 as rwon
    ,parent
    FROM orders c
    INNER JOIN hierarchy c2 ON CAST(c.parent_id AS INTEGER) = c2.id )
SELECT  id, parent_list
FROM    hierarchy
GROUP BY id, parent_list
ORDER BY parent_list;

Çıktı olarak şunu alırız

  id | parent_list                                   
---: | :---------------------------------------------
   2 | Lab Tests                                     
1900 | Lab Tests --> Blood Tests                     
2044 | Lab Tests --> Calcium                         
   1 | Radiology                                     
   3 | Radiology --> Normal Radiology                
   4 | Radiology --> Resonance                       
1100 | Radiology --> Resonance --> Cerebral Resonance