I have table with self-related foreign keys and can not get how I can receive firs child or descendant which meet condition. My_table structure is:

I should for id 1 (union) receive all direct child or first descendant, excluding all groups between first descendant and union. So in this example as result I should receive:

id 4 because it's connected to union through group with id 3 and group with id 2 and id 5 because it's connected directly to union.

I've tried to write recursive query with condition for recursive part: when parent_id = 1 or parent_type = 'depart' but it doesn't lead to expected result

  with recursive cte AS (
  select b.id, p.type_id
  from my_table b 
  join my_table p on p.id = b.parent_id
  where b.id = 1

  union
 
  select c.id, cte.type_id      
  from my_table c
  join cte on cte.id = c.parent_id
  where c.parent_id = 1 or cte.type_id = 'group'
 )

CodePudding user response：

Here's my interpretation:

1. if type='group', then id and parent_id are considered in the same group
2. id#1 and id#2 are in the same group, they're equals
3. id#2 and id#3 are in the same group, they're equals
4. id#1, id#2 and id#3 are in the same group

If the above is correct, you want to get all the first descendent of id#1's group. The way to do that:

1. Get all the ids in the same group with id#1
2. Get all the first descendants of the above group (type not in ('union', 'group'))

with recursive cte_group as (
select 1 as id
union all
select m.id
  from my_table m
  join cte_group g
    on m.parent_id = g.id
   and m.type = 'group')
select mt.id, 
       mt.type
  from my_table mt
  join cte_group cg
    on mt.parent_id = cg.id
   and mt.type not in ('union','group'); 

Result:

id|type  |
-- ------ 
 4|depart|
 5|depart|
 7|unit  |

CodePudding user response：

Sounds like you want to start with the row of id 1, then get its children, and continue recursively on rows of type group. To do that, use

WITH RECURSIVE tree AS (
  SELECT b.id, b.type, TRUE AS skip
  FROM my_table b
  WHERE id = 1
UNION ALL
  SELECT c.id, c.type, (c.type = 'group') AS skip
  FROM my_table c
  JOIN tree p ON c.parent_id = p.id AND p.skip
)
SELECT id, type
FROM tree
WHERE NOT skip