Postgresql recursive query

Question

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

What do you mean by "first descendant"? Rows are not ordered in the relational model (unless you explicitly order them by something). — Bergi
– Bergi, Commented Dec 26, 2022 at 20:16
I mean that it can be direct link between parent record and child, so it will be child, or it can be connection through several records, so the record I should get as result of query not direct child, but descendant. — Vlad Farsiyants
– Vlad Farsiyants, Commented Dec 26, 2022 at 20:59
So you want the closest descendant that is not of type group? But you don't want to get all the descendants? — Bergi
– Bergi, Commented Dec 26, 2022 at 21:03

JHH · Accepted Answer · 2022-12-27 05:16:52Z

1

Here's my interpretation:

if type='group', then id and parent_id are considered in the same group
id#1 and id#2 are in the same group, they're equals
id#2 and id#3 are in the same group, they're equals
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:

Get all the ids in the same group with id#1
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  |

answered Dec 27, 2022 at 5:16

JHH

1,5591 gold badge6 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vlad Farsiyants Over a year ago

Thank you. It's so clear in the end. You understood everything right and your solution is working fine)

Bergi Over a year ago

Instead of mt.type not in ('union','group'), I would have suggested mt.id NOT IN TABLE cte_group

Bergi · Accepted Answer · 2022-12-27 07:36:02Z

1

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

answered Dec 27, 2022 at 7:36

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Collectives™ on Stack Overflow

Postgresql recursive query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related