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

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:

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