0

Solved: Solution from crossposting: https://stackoverflow.com/a/71149082/8082262

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

collection_id community_id
13081d45-dcdc-4e4d-94a5-33056c9f0c49 17475c16-a03e-4f5f-8928-bef6a4813978
cbc98392-ba86-4ed3-b477-5a4a7652f3f0 88aa4abd-d024-47d2-a1a4-bdb3d744dd24

community2community

child_comm_id parent_comm_id
17475c16-a03e-4f5f-8928-bef6a4813978 ae12f497-b7de-4cb6-939f-70968a5e7b0a
ae12f497-b7de-4cb6-939f-70968a5e7b0a 05810e9b-cf09-4c13-a750-297cca5fd84f
88aa4abd-d024-47d2-a1a4-bdb3d744dd24 726a8531-4441-4feb-a8fc-b089445bc39e
726a8531-4441-4feb-a8fc-b089445bc39e ae12f497-b7de-4cb6-939f-70968a5e7b0a
ae12f497-b7de-4cb6-939f-70968a5e7b0a 05810e9b-cf09-4c13-a750-297cca5fd84f
  • 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;
2
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Feb 16, 2022 at 19:57
  • Solution from crossposting the question: stackoverflow.com/a/71149082/8082262 Feb 16, 2022 at 21:24

0

Your Answer

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

Browse other questions tagged or ask your own question.