0

I have two tables, tree_catalog and tree_catalog_link. tree_catalog has the entries of all catalogs, but there are some usecases where I link catalogs to each other. In short, catalogs can be linked from different owners. So supppose User A has 3 catalogs (it can be in hierarchy, A-->C, B), so I can link all those catalogs for another User B. It will follow same hierarchy. So catalog C is a child of catalog A. Now when I link all catalogs to UserB, only catalog A and Catalog B entries will be inserted in the db. Catalog C should be automatically fetched from the hierarchy.

In tree_catalog_link parentId and childId is reference from tree_catalog.

I need help in fetching that. I need to traverse till version 1.

I have tried below two queries but that doesn't provide the required output.

select * from public.tree_catalog_link t1
join public.tree_catalog_link t2 on t1."parentId" = t2."childId" OR t1."parentId" = 1
where t2."accountId" = 8 ;


select * from public.tree_catalog_link tcl1
left join public.tree_catalog_link tcl2 on tcl2."childId" = tcl1."parentId"
left join public.tree_catalog tc1 on tcl2."childId" = tc1."id"
where tc1."ownerId" = 8;

In layman terms, link is

accountId = 8,
catalog of account 8 = 9
catalog 9 is linked from catalog 2,3
catalog 2 has catalog 1 at root level
catalog 3 has catalog 6 inside it
catalog 7 has catalog 7 inside it.

Here I'm looking to fetch all catalogs for accountId = 8. The expected output is catalog 9, 2,3,1,6,7.

1st image is of tree_catalog, 2nd image is of tree_catalog_link

tree catalog db table tree catalog link db table

Akhilesh Mishra
  • 5,876
  • 3
  • 16
  • 32
Ashish Sharma
  • 76
  • 6
  • Have you looked at using recursive queries? https://www.postgresql.org/docs/current/queries-with.html – Blue Star Sep 20 '20 at 05:06
  • didn't understand how we can build the query using that. can you please help in writing the query or pseudo code – Ashish Sharma Sep 20 '20 at 06:47
  • You can take a look how I use the same concept of `closure table` (https://stackoverflow.com/a/38701519/5962802) but I have a single hierarchy for all users - and each user has an attribute which defines his own `root` node inside the global hierarchy. Then the user is able to access everything below his `root` (including the `root` itself). – IVO GELOV Sep 20 '20 at 07:01

0 Answers0