Hierarchical retrieval of records in single query

Question

I am using PostgreSQL 9.2. I have a table of products which stores the information about the products and its child table is content table , which contains the products linked to it.

suppose for example If I have a product called Burger in the main table, Then I will be having its content products like

 bread,cheese,chilli.

Their can be a scenario where bread will be main product and its content will be flour and salt etc.

I am writing a query to retrieve all the products(ids associated with them) with their content products which should display something like this hierarchy

Burger----bread--+----flour
                 +----salt
      ----cheese
      ----chilli

I have to get the result like this

burger bread
burger cheese
burger chilli
bread  flour
bread  salt

This hierarchy can run up to n level (like even floor can have sub content with it in that case it should something like this

burger bread
burger cheese
burger chilli
bread  flour
bread  salt
flour  someprod1
flour someprod2   assuming if someprod1 and someprod2 are the contents)

I have written the following query:

select rec.m_product_id,rec.m_productbom_id 
from rec_product_recipe  rec
join rec_product_recipe rec1
on rec1.m_productbom_id = rec.m_product_id

But this shows up to one level itself as:

burger bread
burger cheese
burger chilli

I think this question would do better on StackOverflow or DBA. Either way, have you tried a recursive CTE? If not look at the `WITH RECURSIVE` statement in the [docs](http://www.postgresql.org/docs/8.4/static/queries-with.html). — Brian.D.Myers, Sep 03 '14 at 16:50
A table definition and some sample values would be very much appreciated with a question like this one. — Erwin Brandstetter, Sep 03 '14 at 21:49

score 0 · Accepted Answer · edited May 23 '17 at 12:15

This is a textbook example for a recursive CTE:

WITH RECURSIVE cte AS (
   SELECT 0 AS lvl, m_product_id, m_productbom_id
   FROM   rec_product_recipe
   WHERE  m_product_id = <id of burger> -- restrict to one root product

   UNION ALL
   SELECT c.lvl + 1, r.m_product_id, r.m_productbom_id
   FROM   cte  c
   JOIN   rec_product_recipe r ON r.m_product_id = c.m_productbom_id
   -- WHERE c.lvl < 10   -- protection against loops (optional)
   )
SELECT *
FROM   cte
ORDER  BY 1,2,3;

Much like this one:

Total children values based on parent

If you have circular dependencies the query will be stuck in an endless loop and eventually raise an exception. If that can happen, add some kind of break condition. Like the maximum level of iterations I added as comment. Or a LIMIT n for the outer SELECT, which also makes the CTE stop looping as soon as enough rows have been retrieved. Examples in the manual.

Hierarchical retrieval of records in single query

1 Answers1