Best way to handle org hierarchy with a tree structure
Our database stores hierarchies for different relationships between organizations and tags as a materialized view or tree value.
Primary orgs<<<<>>>>>> Child orgs
I need to be able to view data at any level which includes a roll-up of all data below it in the hierarchy structure.
I've written a CTE to handle these relationships in the interim but as these tree structures grow a fixed CTE will no longer be adequate.
Any recommendations for a direction to go would be appreciated.
Sounds like a recursive CTE may solve your problem, depending on what flavor of SQL you're writing. To be honest though, I'm not sure I really like the business rules themselves here. Infinitely nested parent / child relationships? Why not set a rational limit, and then a fixed CTE can be written within that limit? Not trying to backsolve for the business rule to make reporting easier, but sometimes reporting complexity is simply a factor of business complexity, and without context, I'm not sure truly infinite stacking is useful here. I'd bet 95% of your use cases are covered by like 3-4 levels of hierarchy.Reply
I agree with Ryan, that you should reconsider your data model.
This could probably be solved, as Ryan said, with recursive CTEs. Another way might be to join to a "numbers" table, i.e. all numbers from 1 to X, where X is the max depth of child relationships.
I'm sure if you gave us a few rows of data we could come up with a more fleshed out solution.Reply
Interesting. I may not be following perfectly, but if you are looking to be able to grab all children by a parent organizationid I have a solution.
I'd prefer to revamp the data model, but you should be able to get what you're looking for with code like below. First step is to create a a table with one row per parent organization, which I can reference to get my data. I'd probably turn the first query, 'parents', into a materialized view on cache.
My second query will give me all the children of orgs 63 and 77, per the where clause.
with parents as ( select organizationid , organizationname , nmbr , hierarchykey , regexp_count(hierarchykey, '/') - 1 as num_children , split_part(hierarchykey, '/', nmbr::integer) as parent_organization from [org_structure] join [_numbers] on nmbr <= regexp_count(hierarchykey, '/') where organizationid is not null and nmbr > 1 order by 1, 3 ) select parent_organization , organizationid as child_organization , organizationname as child_name from parents where parent_organization in ( 63 , 77 ) order by 1,2Reply