Stopping recursive query on first match in Postgres 11

Question

Im struggling a bit with recursive queries in Postgres.

Given these tables:

create table resource_v3
(
 id uuid not null primary key,
 name varchar(255),
 resource_id uuid,
 resource_type varchar(255),
 tenant_id uuid
);

and

create table resource_associations
(
 parent_id uuid not null
 child_id uuid not null
 primary key (parent_id, child_id)
);

Given these tables I built the following structure

enter image description here

What I want to archive is to get all teams or organisations that user1 is in. I only need the first match, so given user1 the recursion should stop at "Team service technician" and "ORG:Seepex". It should not return "ORG:CS" and not "ORG:Seepex-UK"

My query:

WITH RECURSIVE c AS (
 SELECT v.id from resource_v3 v where v.resource_id = '8a84bf66-6dc4-43c7-ace1-d104fddaa1be'
 UNION ALL
 SELECT sa.parent_id
 FROM resource_associations AS sa JOIN c ON c.id = sa.child_id
 JOIN resource_v3 rv3 on c.id = rv3.id
 WHERE rv3.resource_type != 'TEAM' OR rv3.resource_type != 'ORGANISATION'
)
SELECT r.* FROM c JOIN resource_v3 r ON c.id=r.id;

returns more data as I would expect:

enter image description here

What am I doing wrong here or how would you solve this? It seems i ́m amost there, the query just should stop traveling up the tree on first hit.

My final goal is to get all the child elements of the TEAM or ORGANISATION where the given user is in. So the logical plan was to get the orgs / teams with the query above and from there get the child elements recursively, which is fairly easy and works well.

Answer 1

Try the following query. The trick is to continue going up the chain(s) if you haven't found a TEAM or ORGANISATION item by using WHERE c.resource_type NOT IN ('TEAM', 'ORGANISATION') in the recursive part of the CTE.

The WHERE c.resource_type IN ('TEAM', 'ORGANISATION') in the outer query is needed if you want to return only the top (TEAM or ORGANIZATION) items found and neither the starting point (User1) nor the possible intermediate items of different resource type.

WITH RECURSIVE c AS (
 SELECT v.id, v.resource_type 
 from resource_v3 v 
 where v.resource_id = '8a84bf66-6dc4-43c7-ace1-d104fddaa1be'
 UNION ALL
 SELECT sa.parent_id, rv3.resource_type
 FROM resource_associations AS sa 
 JOIN c ON c.id = sa.child_id
 JOIN resource_v3 rv3 on sa.parent_id = rv3.id
 WHERE c.resource_type NOT IN ('TEAM', 'ORGANISATION')
)
SELECT r.* 
FROM c JOIN resource_v3 r ON c.id = r.id
WHERE c.resource_type IN ('TEAM', 'ORGANISATION') ;

• Alex Tbk: Thanks a lot. How would the syntax look like, if i need to use the result of this recursive query in a new one? After finding the TEAM / ORG of the given user, i now would like to travel the tree down from the given element and collect all devices underneath the elements. WITH RECURSIVE c AS ( SELECT uuid('c13836a4-1e38-4105-aefe-4c6a9364791d') as id UNION ALL SELECT sa.child_id FROM resource_associations AS sa JOIN c ON c.id = sa.parent_id ) SELECT r.* FROM c JOIN resource_v3 r ON c.id=r.id
• ypercubeᵀᴹ: You can chain one cte after the other. WITH RECURSIVE c AS (...exactly-what-we-have-in-the-answer...), d AS (SELECT id FROM c UNION ALL SELECT sa.child_id FROM resource_associations AS sa JOIN d ON d.id = sa.parent_id) SELECT r.* FROM d JOIN resource_v3 r ON d.id=r.id;