Feature request - as a user, I'd like to explore easily a hierarchy using recursive joints

[szawadski]

Hierarchies can be represented through the notion of parent_id in tables but it involves the need to create recursive joints to walk up the parents or walk down the children of an element. It is often needed to "pivot" such kind of hierarchical structure defined through child/parent links to filter data based on another level than the one to which the element is actually attached. This applies to organisations, bill of materials, for instance. For instance, I could have workers attached to different levels of an organisation and I may want to know who is linked to a particular division and all department below in the hierarchy:

• having people defined by people_id and department_id
• department defined by department_id and parent_department_id

Pivoting the hierarchy would mean to create the following table based on the above tables: person_id | lev0_department_id | lev1_department_id | lev2_department_id | ...

Any idea on how to express that in a user-friendly but generic way in Malloy language?