Skip to content
Advertisement

Get all ancestors of a child in postgres recursive query

I am trying to get all related ancestors of a child with more info by joining with another table. I’m kind of new to backend so recursive cte is hard to understand.

Find SQL Fiddle here

Data I have : product_id,user_id

Data I need :

user_id master_id cost
id(john_snow) null 4
id(bran_stark) id(john_snow) 6
id(arya_stark) id(bran_stark) 8
id(sansa_stark) id(arya_stark) 10

So that I can add profits to respective users and least cost as company profit.

Advertisement

Answer

A RECURSIVE CTE is what you’re looking for. It might look confusing at the first glance, but after a little practice it becomes self-explanatory. In the end, it is just a UNION with slightly different queries:

WITH RECURSIVE get_ancestor(child,parent,cost) AS (
  SELECT r.user_id,r.parent_id,c.cost FROM user_relation r
  JOIN product_cost c ON c.user_id = r.user_id
  UNION 
  SELECT g.child,g.parent,c.cost FROM get_ancestor g
  JOIN user_relation r ON r.user_id = g.child
  JOIN product_cost c ON c.user_id = r.user_id 
)
SELECT * FROM get_ancestor;

Demo: SQL Fiddle

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement