Page Comparison

...

...

...

...

...

...

This document explains how to use recursive joins in a database to model hierarchical data, specifically focusing on user hierarchies and chart aggregation in Panintelligence.

• The traditional method of unpacking hierarchical data using multiple self-joins is limited by the need to guess maximum depth and struggles with aggregating values up the hierarchy.

• A new method using drill-to-chart functionality in Panintelligence allows traversal of any depth hierarchy and exposes details at the lowest levels.

• This new method requires three charts/tables: a top-level chart, a recursive drill chart, and a detail table. Chart filters and recursive drill steps are used to implement the hierarchy.

• For aggregating data up the hierarchy (e.g., summing chart counts for a user and their descendants), a recursive CTE (Common Table Expression) is used in the data connection, overcoming limitations of the traditional method.

What is a recursive join?

...

In This case I will now use a recursive CTE - See below for DB Support

A CTE (Common Table Expression) is a temporary, named result set in SQL that is defined within a query and can be referenced later in that query. It improves query readability, simplifies complex queries, and allows for recursive queries in cases such as hierarchical data processing.

The code I require for my Recursive CTE is

WITH RECURSIVE Hierarchy AS (
     SELECT 
        SERIAL AS ID,        
        SERIAL AS RootID,
        0 AS Depth
    FROM 
        MIS_USERS
    UNION ALL
    SELECT 
        child.SERIAL AS ID,
        parent.RootID AS RootID,
        parent.Depth + 1 AS Depth
    FROM 
        MIS_USERS child
    INNER JOIN 
        Hierarchy parent ON child.PARENT_ID = parent.ID
WHERE 
        parent.Depth < 10 -- Required break point in case of infinite loop, where descendants become parents, data quality issue 
)
SELECT 
    RootID,  
    ID,
    Depth
FROM 
    Hierarchy
WHERE
  RootID = 34 -- The entry point (Greta = 4) , this does not need to start at the top node
ORDER BY 
    RootID, ID

...

Then we use this as a join between MIS_USERS and the table we want to aggregate over MIS_DEFINED_CHARTS

...

Explanation:

The recursive CTE unpacks data, so If I run the SQL for user serial 4 (Greta)

...

The query runs 3 rows. One for Greta, and a row for each descendant. Ronan + George.

If I add more nested users under Greta.

...

The return now becomes

...

As it includes all descendants no matter their depth (there is an exit @10 to protect an infinite loop in case of poor data quality - that is optional / up to you)

We are using this as a link table in our structure as it means that as Greta - I get all items (charts) linked to all my descendants.

It also allows us to count the number of ROOT_ID - 1, as if this > 0 Then there are descendants, so we can use this to exit our recursive drill path.

Limitatations;

Not All databases support CTES' and recurusive CTE’s - though this is a common pattern that continues to be adopted by more and more technologies. As of Jan 2025 this is the current state of play;

Databases that support CTE’s - and Recursive CTE

...

Databases / Extent of CTE support

...