"REAL WORLD" SQL CTE query capable of: getting a tree from a parent child table with depth-first & alphabetical ordering & filter/sum nodes subtrees

Question

After searching hours over the internet, still missing a good example of a SQL CTE Query able to be used in real world scenarios, wondering even if CTE and MODERN SQL in general are currently capable without other techniques like "materialized paths" and others to achive this.

The query must have at least this features:

• getting a depth-first tree ordered list out of parent child tables (tables with columns id/parent_id)
• keeping a depth-first tree must order by title/name each node at the same level
• being able to sum() count() columns of a subtree into each ancestor node (row speaking in sql terms) this obviously because it's the only way to filter subtrees for certain conditions (like: any category with published posts for a cms)

Many "solutions" over stackoverflow and event mysql blog are half-baked and even the good ones hidden ins the interweb at best check the first requirement and sometimes the second one.

like: https://dev.mysql.com/blog-archive/mysql-8-0-1-recursive-common-table-expressions-in-mysql-ctes-part-four-depth-first-or-breadth-first-traversal-transitive-closure-cycle-avoidance/

Answer 1

See this example query in the MySQL dialect (but it's mostly the same for all databases) that works on the well-known Taxonomy table schema of WordPress. (It uses 2 tables for legacy reasons but it's not important, any table with: id/parent_id/name/count or flags columns should work well!)

    WITH RECURSIVE

    cte_rn AS
    (
      SELECT
        tt.term_id,
        tt.parent,
        t.name,
        tt.count,
        ROW_NUMBER() OVER (PARTITION BY tt.parent ORDER BY t.name ASC) AS rn
      FROM
        wp_term_taxonomy tt
      INNER JOIN
        wp_terms t ON tt.term_id = t.term_id
      WHERE
        tt.taxonomy = 'category'
    ),

    cte AS
    (
      SELECT
        a.*,
        CAST(LPAD(a.rn,4,'0') AS CHAR(200)) AS path,
        0 AS depth
      FROM
        cte_rn a
      WHERE
        a.parent = 0
      
      UNION ALL

      SELECT
        b.*,
        CONCAT_WS(",",cte.path, LPAD(b.rn,4,'0')),
        cte.depth+1
      FROM
        cte_rn b
      INNER JOIN
        cte ON b.parent = cte.term_id
    )

    SELECT
      c1.*,
      c1.count + SUM(IFNULL(c2.count,0)) AS ncount
    FROM
      cte c1
    LEFT OUTER JOIN
      cte c2 ON c1.path <> c2.path AND LEFT(c2.path, LENGTH(c1.path)) = c1.path
    GROUP BY path
    HAVING ncount > 0
    ORDER BY c1.path