2

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.

0

1 Answer 1

3

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') ;
2
  • 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;
    – Alex Tbk
    Sep 16, 2022 at 9:50
  • 2
    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; Sep 16, 2022 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.