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

Postgresql recursive query

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:

id parent_id type
1 null union
2 1 group
3 2 group
4 3 depart
5 1 depart
6 5 unit
7 1 unit

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 type
4 depart
5 depart
7 unit

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'
 )
Tags:

2

Answers


  1. 0

    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'))

    enter image description here

    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  |
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search