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 );
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"
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.