I am trying to fetch end child data of grandparent data.

I have below 2 tables, one is master table having parent and child data, other is the relation table for that master table data.

based on these data, I want parent data with their end child data.

for that I have tried below recursive query but not geting any related data.

At last I want to use those data in below way with java batch.

if I pass child_data 331 and 327 one by one then it will provide below results respectively.

@set ko_id = '331'

select parent_id,child_id,count(parent_id) from (
 WITH RECURSIVE ancestors (parent_id) AS (
  SELECT distinct t.parent_id ,t.parent_id as extra_id,t.child_id , msok.data_type -- and find all its ancestors
  FROM public.data_relation AS t 
    JOIN data_relation AS a ON t.child_id = a.parent_id or t.child_id = a.child_id 
    left join data_master msok on msok.id = t.parent_id
  where a.child_id = :ko_id
), 
descendants (parent_id) AS (
  SELECT  parent_id ,extra_id as extra_id,child_id, data_type FROM ancestors  
  UNION ALL
  SELECT t.child_id,d.parent_id as extra_id,t.child_id, msok.data_type -- and find all their descendants
  FROM public.data_relation AS t 
    JOIN descendants AS d ON t.parent_id = d.parent_id
    left join data_master msok on msok.id = t.child_id 
) 
SELECT 
  parent_id, extra_id, child_id, data_type
FROM 
  descendants where data_type ='1') abc group by parent_id,child_id

CodePudding user response：

This query should help. The first CTE ("with" part) provides all relations there are in the table. The second CTE filters only end child records out.

And the final part - the main query - searches for parents having 331 as a child and shows you those.

with recursive result_q as (
  select 1 debug_step,  parent_data, child_data 
    from data_rel
  union all
  select debug_step   1, q.parent_data, r.child_data
    from data_rel r
    join result_q q
      on q.child_data = r.parent_data 
),
parent_end_childs as (
  select q.parent_data, q.child_data 
    from result_q q
   where not exists (select 1 from data_rel r where r.parent_data = q.child_data)
   order by parent_data, child_data)
   
select pec.*
  from parent_end_childs pec
  join parent_end_childs pec_1
    on pec.parent_data = pec_1.parent_data
 where pec_1.child_data = 331

dbfiddle

CodePudding user response：

You can try this which creates the multilevel parent-child relationships as jsonb arrays :

WITH RECURSIVE list (array_parent_child_data) AS
(
    SELECT to_jsonb(array[t.parent_data, t.child_data])
      FROM public.data_relation AS t
  UNION ALL
    SELECT t.parent_data || l.array_parent_child_data
      FROM public.data_relation AS t
     INNER JOIN list AS l
        ON l.array_parent_child_data->0 = t.child_data
       AND NOT l.array_parent_child_data @> to_jsonb(t.parent_data) -- this condition is to avoid loops
)
SELECT l2.array_parent_child_data->>0 :: integer AS parent_data
     , l2.array_parent_child_data->>-1 :: integer AS end_child_data
  FROM list AS l1
  LEFT JOIN list AS l2
    ON l1.array_parent_child_data <> l2.array_parent_child_data
   AND l1.array_parent_child_data @> l2.array_parent_child_data
 WHERE l1 IS NULL  -- l2.array_parent_child_data is not included in any l1.array_parent_child_data