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

/1/2/3/4/5/6.... /#/..

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.

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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 Like 1
  • 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 Like
  • Here is a bit of test data involving the deepest hierarchy from our current set-up which is likely to grow deeper. 

    Appreciate the responses very very much! 

    Reply Like
  • 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,2
    Reply Like 1
Like Follow
  • 1 yr agoLast active
  • 4Replies
  • 1033Views
  • 4 Following