I have two tables: community2collection(collection_id,community_id) and community2community(child_comm_id,parent_comm_id).

I'm trying to create a recursive view that for any given collection (collection_id) will return a list of the direct community parent (community_id), as well as any ancestor of the direct community (so recursively going through community2community.

I had

CREATE VIEW hierarchical_comcoltree
AS
    WITH RECURSIVE comcoltree AS
      (SELECT collection_id,
              community_id AS any_community_id
       FROM community2collection
       UNION ALL SELECT com2col.collection_id,
                        com2com.parent_comm_id AS any_community_id
       FROM community2collection com2col
       INNER JOIN community2community com2com
       ON com2com.child_comm_id = com2col.community_id)
    SELECT * FROM comcoltree;

But this only retrieves the direct community, as well as its first parent, but not further ancestors, with query

SELECT collection_id,
       any_community_id
FROM hierarchical_comcoltree
WHERE collection_id = '13081d45-dcdc-4e4d-94a5-33056c9f0c49'
ORDER BY collection_id ASC;

Any help would be appreciated, tried a few variations (as well as with a function), but haven't found one that works yet...

Test data

community2collection

community2community

• Expected results view for first collection '13081d45-dcdc-4e4d-94a5-33056c9f0c49'

=> '17475c16-a03e-4f5f-8928-bef6a4813978', 'ae12f497-b7de-4cb6-939f-70968a5e7b0a' and '05810e9b-cf09-4c13-a750-297cca5fd84f'

=> Missing last one with current solution

• Expected results view for second collection 'cbc98392-ba86-4ed3-b477-5a4a7652f3f0'

=> '88aa4abd-d024-47d2-a1a4-bdb3d744dd24', '726a8531-4441-4feb-a8fc-b089445bc39e', 'ae12f497-b7de-4cb6-939f-70968a5e7b0a' and '05810e9b-cf09-4c13-a750-297cca5fd84f'

=> Missing last two ones with current solution

Testing with:

WITH RECURSIVE comcoltree AS
  (SELECT collection_id,
          community_id AS any_community_id
   FROM community2collection
   UNION ALL SELECT com2col.collection_id,
                    com2com.parent_comm_id AS any_community_id
   FROM community2collection com2col
   INNER JOIN community2community com2com ON com2com.child_comm_id = com2col.community_id)
SELECT collection_id,
       any_community_id
FROM comcoltree
WHERE any_community_id = 'cbc98392-ba86-4ed3-b477-5a4a7652f3f0'
ORDER BY collection_id ASC;

CodePudding user response：

This is the best I have with the provided information:

WITH RECURSIVE comcoltree AS
(
    SELECT collection_id
         , community_id     AS any_community_id
      FROM community2collection
     WHERE collection_id = 'cbc98392-ba86-4ed3-b477-5a4a7652f3f0'
     UNION ALL
    SELECT prev.collection_id
         , next.parent_comm_id AS any_community_id
      FROM comcoltree prev
      JOIN community2community next ON prev.any_community_id = next.child_comm_id
)
SELECT DISTINCT 
       collection_id
     , any_community_id
  FROM comcoltree
;

Here is a DBFiddle to show it working. I threw the distinct in there because there were duplicates in the result set but I think that's due to having a duplicate in the sample data.

ae12f497-b7de-4cb6-939f-70968a5e7b0a 05810e9b-cf09-4c13-a750-297cca5fd84f