Hierarchical search for records in a single query
I am using PostgreSQL 9.2. I have a products table that stores product information and its child table is a content table that contains related products.
suppose, for example, if I have a product called Burger in the main table, then I will have its content such products as
bread,cheese,chilli.
They may be a scenario where bread is the main product, and its contents are flour and salt, etc.
I am writing a query to retrieve all products (ids associated with them) with their content products, which should display something like this hierarchy
Burger----bread--+----flour
+----salt
----cheese
----chilli
I need to get a result like this
burger bread
burger cheese
burger chilli
bread flour
bread salt
This hierarchy can work up to n level (for example, even gender can have sub-content with it, in which case it should be 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 wrote the following request:
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 it manifests itself to the very level:
burger bread
burger cheese
burger chilli
source to share
This is a tutorial example for 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 similar to this:
If you have circular dependencies, the request will depend on an infinite loop and eventually throw an exception. If this can happen, add some kind of abort condition. As well as the maximum iteration level I added as a comment. Or LIMIT n
for the external SELECT
, which also makes the CTE loop stop as soon as enough rows have been received. Examples in the manual .
source to share