Search code examples
sqlpostgresqlcommon-table-expressionrecursive-query

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.


Solution

  • 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 two 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