PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to use recursive SQL to find the parent ID

I’m a noob in the recursive domain in that I understand the syntax but I’m unable to apply it to my current problem. I’m using Postgre SQL.

I have a product_id ‘ABC123’ that has a parent category ‘FG_ACC_CA’, which itself has parent category ‘FG_ACC’, which also has a parent category ‘FG’. The ‘FG’ does not have a parent category.

I’m trying to use recursive query to find the top most parent of the given product_id.
Here parent_id & product_id are columns and product_category & product_product are their respective tables.

The syntax that I’m trying to use is this
enter image description here

Here’s the query I’ve written:

with recursive ancestor as 
(
    select pc.parent_id as temp
    from product_product pp 
    left join product_category pc on pc.id = pp.categ_id
    where pp.product_id = 'ABC123'

    union

    select temp
    from ancestor a
    where a.temp != Null
)
select *
from ancestor
;

The results I get is just the parent_id of ABC123. So I guess I’m butchering the recursive part after union.

Also while researching I’ve come across multiple parent-child examples but they all use ‘inner join’ instead of ‘where’ even though they explain the concept with ‘where’ as a recursive condition. Couldn’t figure out why.

Tags:

2

Answers


  1. 0

    In the recursive clause, the recursive table (product_category) must be included in the query and joined with the results of the recursion (ancestor).

    with recursive ancestor as (
    select pc.parent_id as temp
    from product_product pp
    left join product_category pc on pc.id = pp.categ_id
    where pp.product_id = 'ABC123'

    union all

    select pc.parent_id as temp
    from product_category pc
    inner join ancestor a on a.temp = pc.id
    where a.temp is not null
)
select temp from ancestor;
    Login or Signup to reply.
  2. 0

    Without some sample data it is hard to offer an answer.
    If your data looks something like this:

    product_category table

    id parent_id category
    1 1 FG
    2 1 FG_ACC
    3 2 FG_ACC_CA
    4 4 GG
    5 4 GG_BDD

    product_product table

    id product category_id
    11 ABC123 3
    12 DEF456 5

    … no need for recursion – you could use self joins to get the root category of your products …

    WITH
  ancestors AS
    ( Select    pc_0.id, pc_0.parent_id as root_id, 
                pc_0.parent_id, pc_0.category, 
                pc_1.id as child_id,
                pp.product, pp.id as product_id
      From      product_category pc_0
      Left Join product_category pc_1 ON( pc_1.parent_id = pc_0.id )
      Left Join product_product pp ON( pp.category_id = pc_1.id )
      Where     pc_0.parent_id IN( Select id From product_category Where id = parent_id ) And
                pp.id Is Not Null 
  ) 
Select  DISTINCT 
            a.product_id, a.product, a.root_id, pc.category as root_category
From        ancestors a
Inner Join  product_category pc ON( pc.id = a.root_id )
Order By    a.product_id

    R e s u l t :

    product_id product root_id root_category
    11 ABC123 1 FG
    12 DEF456 4 GG

    … if your product could be deeper in hierarchy ( added root 0 ) …

    product_category table ( added id 0 )

    id parent_id category
    0 0 F
    1 0 FG
    2 1 FG_ACC
    3 2 FG_ACC_CA
    4 4 GG
    5 4 GG_BDD

    … you will need to add another selfjoin ( pc_2 ) and adjust ON conditions for pp table …

    WITH
  ancestors AS
    ( Select    pc_0.id, pc_0.parent_id as root_id, 
                pc_0.parent_id, pc_0.category, 
                pc_1.id as child_id, pc_2.id as grandchild_id,
                pp.product, pp.id as product_id
      From      product_category pc_0
      Left Join product_category pc_1 ON( pc_1.parent_id = pc_0.id )
      Left Join product_category pc_2 ON( pc_2.parent_id = pc_1.id )
      Left Join product_product pp ON( pp.category_id IN( pc_1.id, pc_2.id ) )
      Where     pc_0.parent_id IN( Select id From product_category Where id = parent_id ) And
                pp.id Is Not Null 
  ) 
Select  DISTINCT 
            a.product_id, a.product, a.root_id, pc.category as root_category
From        ancestors a
Inner Join  product_category pc ON( pc.id = a.root_id )
Order By    a.product_id

    R e s u l t :

    product_id product root_id root_category
    11 ABC123 0 F
    12 DEF456 4 GG

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search