fetching tree structured data from postgresql

Question

Im figuring out which is the efficient way to fetch tree-structured data

i have a table like this

Table : food_categories

----------------------------------------------------------
| id     | parent       | category_name                  |
----------------------------------------------------------
| 1        0              Food                           |
| 2        1              Veg Items                      |
| 3        1              Non Veg Items                  |
| 4        2              Carrots                        |
| 5        2              Greens                         |
| 6        2              Milk                           |
| 7        3              Poultry                        |
| 8        3              Seafood                        |
| 9        7              Chicken                        |
| 10       8              Fish                           |
| 11       8              Prawns                         |
----------------------------------------------------------

the tree-structure depth is not limited here, it can go to any levels

Im thinking to fetch these like below

array(Food'=>array( 'Veg Items'=>array('carrots'=>array(),'Greens'=>array(),'Milk'=>array()),
                    'Non Veg Items'=>array(
                                            'Poultry'=>array('Chicken'=>array()),
                                            'Seafood'=>array('Fish'=>array(),'Prawns'=>array())
                                            )
                                    )
)

is this possible to fetch this kind of structured array?

Im using postgresql,but Im not very handy on this, read many questions here in SO and other articles explaining the similar concepts but I couldnt get exactly.

help is appreciated.

Possible duplicate of [What is the most efficient/elegant way to parse a flat table into a tree?](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree) — bufh, Apr 11 '16 at 13:04

score 1 · Accepted Answer · answered Apr 11 '16 at 14:13

In Postgres you can do this with a recursive CTE:

WITH RECURISVE recCTE AS
(
    --Recursive seed
    SELECT
        parent,
        id as child,
        0 as depth
        parent || '>' || id as path
    FROM
        food_categories
    WHERE parent = 0 --restricting to the top most node of your hierarchy

    UNION ALL

    --Recursive statement
    SELECT
        recCTE.child as Parent,
        fc.id as child,
        recCTE.depth + 1 as Depth,
        path || '>' || fc.id as path
    FROM
        recCTE 
        INNER JOIN food_categories fc 
            ON recCTE.child = fc.parent
    WHERE
        depth <=20 --Set this just in case we get into an infinite cycle
)

SELECT * FROM recCTE;

The recursive CTE takes three parts:

The recursive seed, which is the starting point of your Hierarchy. I'm guessing in your case it's a parent of 0
The recursive term, which is the bit of the recursive CTE that refers back to itself, joining to the tables that hold your hierarchy
The final SELECT to tell Postgres how to select from your CTE.

This will get back every node in your hierarchy, it's depth, it's parent, and the path from your root node 0 to the lowest child regardless of depth (up to 20 since we stuck that in the WHERE).

You can combine this with json_agg and row_to_json and the like to transform it into a more usable object in your code, or leave it as is to grab the bits you need from it with that last SELECT statement. If your interested in this route, you can check out this great explaination and example.

fetching tree structured data from postgresql

1 Answers1