SQL (postgres) - Recursive view for all parents of a child

Question

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;