MySQL Common Table Expressions: Recursive Queries for Hierarchical Data

Home » MySQL » MySQL Common Table Expressions: Recursive Queries for Hierarchical Data
July 20, 2025 MySQL
MySQL Common Table Expressions: Recursive Queries for Hierarchical Data

WordPress stores category and comment hierarchies with a parent column, but querying the full tree at arbitrary depth with a single SQL statement requires recursive CTEs — available in MySQL 8.0+ and MariaDB 10.2+. A recursive CTE consists of an anchor member (the root rows) and a recursive member (each subsequent level) joined with UNION ALL.

Problem: WordPress hierarchical data — categories with subcategories, menus with sub-menus, comment threads — is stored as flat rows in MySQL but needs to be queried as a tree structure, which recursive PHP loops achieve slowly with N+1 queries.

Solution: Use MySQL Recursive Common Table Expressions (CTEs) — WITH RECURSIVE cte AS (anchor UNION ALL recursive_step) — to traverse a hierarchy in a single query. For wp_terms, join with wp_term_taxonomy on parent to walk the tree in SQL. Use $wpdb->get_results() with $wpdb->prepare() for safe parameterisation.


The queries below fetch a full WordPress category tree starting from a given root, calculate the depth of each node, build the breadcrumb path as a string, and detect circular references — a safety guard when the data is user-managed.


-- 1. Fetch full category subtree from a given root (term_id = 5)
WITH RECURSIVE category_tree AS (
    -- Anchor: the root category
    SELECT
        t.term_id,
        t.name,
        tt.parent,
        0          AS depth,
        CAST( t.name AS CHAR(1000) ) AS breadcrumb
    FROM   wp_terms t
    JOIN   wp_term_taxonomy tt ON tt.term_id = t.term_id
    WHERE  tt.taxonomy = 'category'
      AND  tt.parent   = 0          -- top-level only; or: AND t.term_id = 5

    UNION ALL

    -- Recursive member: children of the current level
    SELECT
        t.term_id,
        t.name,
        tt.parent,
        ct.depth + 1,
        CONCAT( ct.breadcrumb, ' > ', t.name )
    FROM   wp_terms t
    JOIN   wp_term_taxonomy tt ON tt.term_id = t.term_id
    JOIN   category_tree    ct ON tt.parent  = ct.term_id
    WHERE  tt.taxonomy = 'category'
)
SELECT term_id, REPEAT('  ', depth) || name AS indented_name, breadcrumb, depth
FROM   category_tree
ORDER  BY breadcrumb;

-- 2. Recursive comment thread (all descendants of comment_ID = 10)
WITH RECURSIVE comment_thread AS (
    SELECT comment_ID, comment_parent, comment_content, comment_author, 0 AS depth
    FROM   wp_comments
    WHERE  comment_ID = 10

    UNION ALL

    SELECT c.comment_ID, c.comment_parent, c.comment_content, c.comment_author,
           ct.depth + 1
    FROM   wp_comments      c
    JOIN   comment_thread   ct ON c.comment_parent = ct.comment_ID
)
SELECT depth, comment_ID, comment_author,
       SUBSTR( comment_content, 1, 60 ) AS excerpt
FROM   comment_thread
ORDER  BY depth, comment_ID;

-- 3. Guard against cycles: limit recursion depth to 20
WITH RECURSIVE safe_tree AS (
    SELECT term_id, parent, 0 AS depth
    FROM   wp_term_taxonomy
    WHERE  parent = 0 AND taxonomy = 'category'

    UNION ALL

    SELECT tt.term_id, tt.parent, st.depth + 1
    FROM   wp_term_taxonomy tt
    JOIN   safe_tree        st ON tt.parent = st.term_id
    WHERE  tt.taxonomy = 'category'
      AND  st.depth < 20    -- hard safety cap
)
SELECT * FROM safe_tree;


NOTE: MySQL 8.0 limits recursive CTE iterations with the cte_max_recursion_depth variable (default 1000); set it to a lower value per session (SET cte_max_recursion_depth = 50) when processing untrusted hierarchical data to prevent runaway queries from user-controlled inputs.

Related posts