Populating a Hierarchical Tree from a Flat Table in SQLite

Understanding the Hierarchical Data Structure and Sorting Requirements

The core issue revolves around transforming a flat table into a hierarchical tree structure in SQLite, where each node in the tree represents a category with a specific sorting order. The table PagesTreeTT1 contains columns such as ID_Page, TxtID_Page, ParentPage_Id, PgSlug, PgDescr, and PgSort. The ParentPage_Id column defines the parent-child relationships, with NULL indicating a top-level category. The PgSort column determines the sorting order of categories at each level.

The desired output is a tree structure where categories are nested under their respective parents, and the sorting is applied at each level based on the PgSort value. For example, the output should resemble:

10
 10-10
 10-20
    10-20-10
    10-20-25
 10-30
    10-30-10
    10-30-15
    10-30-35
20
 20-05
    20-05-10
    20-05-15

The challenge lies in correctly constructing this hierarchy while maintaining the sorting order at each level. Additionally, there are issues with the sorting order not being respected in certain subgroups and problems with concatenated fields in the output.

Identifying the Causes of Sorting and Concatenation Issues

The sorting issue arises because the recursive query does not enforce the sorting order at each level of the hierarchy. In SQLite, recursive queries using WITH RECURSIVE can traverse hierarchical data, but they do not inherently maintain a specific order at each level. The sorting must be explicitly applied within the recursive query to ensure that categories are ordered correctly at each level.

The concatenation issue occurs when attempting to include the ParentPage_Id field in the output. The problem is likely due to the way SQLite handles concatenation with NULL values. When concatenating strings, if any part of the concatenation is NULL, the entire result becomes NULL. This explains why the BranchString field is NULL for top-level categories when ParentPage_Id is included in the concatenation.

Detailed Troubleshooting Steps, Solutions, and Fixes

Step 1: Correcting the Sorting Order

To address the sorting issue, the recursive query must be modified to ensure that the sorting order is applied at each level. This can be achieved by including an ORDER BY clause within the recursive part of the query. The following query demonstrates how to enforce sorting at each level:

WITH RECURSIVE under_root(ID_Page, level, lbl, slug, sort, parid) AS (
  SELECT
    PagesTreeTT1.ID_Page, 
    0, 
    PagesTreeTT1.PgDescr, 
    PagesTreeTT1.TxtID_Page, 
    PagesTreeTT1.PgSort, 
    PagesTreeTT1.ParentPage_Id
  FROM PagesTreeTT1
  WHERE PagesTreeTT1.ParentPage_Id IS NULL
  UNION ALL
  SELECT 
    PagesTreeTT1.ID_Page, 
    under_root.level + 1, 
    PagesTreeTT1.PgDescr, 
    PagesTreeTT1.TxtID_Page, 
    PagesTreeTT1.PgSort, 
    PagesTreeTT1.ParentPage_Id
  FROM PagesTreeTT1 
  JOIN under_root ON PagesTreeTT1.ParentPage_Id = under_root.ID_Page
  ORDER BY under_root.level, PagesTreeTT1.PgSort
)
SELECT 
  (substr('..........', 1, level * 3) || lbl) AS BranchString, 
  slug, 
  sort
FROM under_root
ORDER BY sort;

In this query, the ORDER BY under_root.level, PagesTreeTT1.PgSort clause ensures that the categories are sorted by their level and then by their PgSort value. This should resolve the sorting issue within subgroups.

Step 2: Fixing the Concatenation Issue

To fix the concatenation issue, we need to handle NULL values properly. SQLite provides the IFNULL function, which can be used to replace NULL values with an empty string or another placeholder. The following query demonstrates how to use IFNULL to prevent the BranchString from becoming NULL:

WITH RECURSIVE under_root(ID_Page, level, lbl, slug, sort, parid) AS (
  SELECT
    PagesTreeTT1.ID_Page, 
    0, 
    PagesTreeTT1.PgDescr, 
    PagesTreeTT1.TxtID_Page, 
    PagesTreeTT1.PgSort, 
    PagesTreeTT1.ParentPage_Id
  FROM PagesTreeTT1
  WHERE PagesTreeTT1.ParentPage_Id IS NULL
  UNION ALL
  SELECT 
    PagesTreeTT1.ID_Page, 
    under_root.level + 1, 
    PagesTreeTT1.PgDescr, 
    PagesTreeTT1.TxtID_Page, 
    PagesTreeTT1.PgSort, 
    PagesTreeTT1.ParentPage_Id
  FROM PagesTreeTT1 
  JOIN under_root ON PagesTreeTT1.ParentPage_Id = under_root.ID_Page
  ORDER BY under_root.level, PagesTreeTT1.PgSort
)
SELECT 
  (substr('..........', 1, level * 3) || IFNULL(lbl, '') || ' ' || ID_Page || ' < ' || IFNULL(parid, '')) AS BranchString, 
  slug, 
  sort
FROM under_root
ORDER BY sort;

In this query, the IFNULL function is used to replace NULL values in lbl and parid with an empty string. This ensures that the concatenation does not result in NULL values, and the BranchString is correctly constructed for all levels of the hierarchy.

Step 3: Combining Sorting and Concatenation Fixes

The final step is to combine both fixes into a single query that addresses both the sorting and concatenation issues. The following query demonstrates how to achieve this:

WITH RECURSIVE under_root(ID_Page, level, lbl, slug, sort, parid) AS (
  SELECT
    PagesTreeTT1.ID_Page, 
    0, 
    PagesTreeTT1.PgDescr, 
    PagesTreeTT1.TxtID_Page, 
    PagesTreeTT1.PgSort, 
    PagesTreeTT1.ParentPage_Id
  FROM PagesTreeTT1
  WHERE PagesTreeTT1.ParentPage_Id IS NULL
  UNION ALL
  SELECT 
    PagesTreeTT1.ID_Page, 
    under_root.level + 1, 
    PagesTreeTT1.PgDescr, 
    PagesTreeTT1.TxtID_Page, 
    PagesTreeTT1.PgSort, 
    PagesTreeTT1.ParentPage_Id
  FROM PagesTreeTT1 
  JOIN under_root ON PagesTreeTT1.ParentPage_Id = under_root.ID_Page
  ORDER BY under_root.level, PagesTreeTT1.PgSort
)
SELECT 
  (substr('..........', 1, level * 3) || IFNULL(lbl, '') || ' ' || ID_Page || ' < ' || IFNULL(parid, '')) AS BranchString, 
  slug, 
  sort
FROM under_root
ORDER BY sort;

This query ensures that the hierarchical tree is correctly constructed with the proper sorting order at each level and that the BranchString is correctly formatted without NULL values.

Conclusion

By addressing the sorting and concatenation issues separately and then combining the fixes, we can successfully transform the flat table into a hierarchical tree structure in SQLite. The key takeaways are:

1. Sorting Order: Ensure that the sorting order is explicitly applied at each level of the hierarchy using the ORDER BY clause within the recursive query.
2. Concatenation Handling: Use the IFNULL function to handle NULL values in concatenated fields, preventing the entire result from becoming NULL.

With these adjustments, the SQLite query will produce the desired hierarchical tree structure with the correct sorting and formatting.