Shocm's Blog
• 7 min read

MySQL Recursive CTEs for PHP Developers: Querying Hierarchical Data Without the Pain

Recursive CTEs in MySQL let you query trees, org charts, and nested categories in a single query. Here's how to use them effectively with PHP.

mysql database php laravel sql tips
Featured image for "MySQL Recursive CTEs for PHP Developers: Querying Hierarchical Data Without the Pain"

Every PHP developer eventually hits the “tree problem.” You have categories with subcategories, employees with managers, comments with replies, or menu items with children — and you need to query the entire hierarchy. The traditional PHP approach is either multiple queries in a loop (slow), loading everything into memory and building the tree in PHP (wasteful), or using the nested set model (complex to maintain). MySQL’s recursive Common Table Expressions solve this cleanly in a single query, and they’ve been available since MySQL 8.0.

If you’ve been avoiding CTEs because the syntax looks intimidating, this guide will walk you through practical patterns you can drop into your PHP applications today.

The Basic Syntax

A recursive CTE has two parts: the anchor member (your starting point) and the recursive member (how to find the next level). They’re connected with UNION ALL:

WITH RECURSIVE tree AS (
    -- Anchor: start with root nodes
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive: join children to the current level
    SELECT c.id, c.name, c.parent_id, t.depth + 1
    FROM categories c
    INNER JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;

The anchor query runs once and returns the root nodes. MySQL then repeatedly executes the recursive part, joining against the results from the previous iteration, until no new rows are produced. The depth column tracks how deep each node is in the hierarchy.

Real-World Pattern: Category Breadcrumbs

One of the most common uses is building breadcrumbs. Given a leaf category, walk up the tree to the root:

WITH RECURSIVE breadcrumb AS (
    -- Start from the current category
    SELECT id, name, parent_id, 1 AS level
    FROM categories
    WHERE id = :category_id

    UNION ALL

    -- Walk up to each parent
    SELECT c.id, c.name, c.parent_id, b.level + 1
    FROM categories c
    INNER JOIN breadcrumb b ON c.id = b.parent_id
)
SELECT * FROM breadcrumb ORDER BY level DESC;

This walks from the leaf node upward. The ORDER BY level DESC gives you root-first ordering, perfect for rendering Home > Electronics > Phones > Smartphones.

In Laravel, you can run this with a raw query or use DB::select():

$breadcrumbs = DB::select("
    WITH RECURSIVE breadcrumb AS (
        SELECT id, name, parent_id, 1 AS level
        FROM categories
        WHERE id = ?

        UNION ALL

        SELECT c.id, c.name, c.parent_id, b.level + 1
        FROM categories c
        INNER JOIN breadcrumb b ON c.id = b.parent_id
    )
    SELECT * FROM breadcrumb ORDER BY level DESC
", [$categoryId]);

Each row in $breadcrumbs is a stdClass object with id, name, parent_id, and level properties. You can render them directly in your Blade template:

<nav class="breadcrumbs">
    @foreach ($breadcrumbs as $crumb)
        @if (!$loop->last)
            <a href="{{ route('category.show', $crumb->id) }}">{{ $crumb->name }}</a>
            <span>/</span>
        @else
            <span class="current">{{ $crumb->name }}</span>
        @endif
    @endforeach
</nav>

Pattern: Full Subtree With Path

Sometimes you need to fetch an entire subtree and know the full path to each node. This is useful for sitemaps, admin panels, and export features:

WITH RECURSIVE subtree AS (
    SELECT id, name, parent_id, 0 AS depth,
           CAST(name AS CHAR(1000)) AS path
    FROM categories
    WHERE id = :root_id

    UNION ALL

    SELECT c.id, c.name, c.parent_id, s.depth + 1,
           CONCAT(s.path, ' > ', c.name)
    FROM categories c
    INNER JOIN subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree ORDER BY path;

The path column accumulates the full breadcrumb trail for every node. The CAST in the anchor is important — without it, MySQL may truncate the concatenated path in deeper hierarchies. Adjust the length as needed for your data.

Pattern: Organizational Chart Queries

Recursive CTEs shine for org chart queries. Find all employees who report to a specific manager, directly or indirectly:

function getAllReports(int $managerId): Collection
{
    $reports = DB::select("
        WITH RECURSIVE reports AS (
            SELECT id, name, manager_id, title, 1 AS level
            FROM employees
            WHERE manager_id = ?

            UNION ALL

            SELECT e.id, e.name, e.manager_id, e.title, r.level + 1
            FROM employees e
            INNER JOIN reports r ON e.manager_id = r.id
        )
        SELECT * FROM reports ORDER BY level, name
    ", [$managerId]);

    return collect($reports);
}

You can count total reports per manager, calculate depth of the reporting chain, or filter to a specific level — all within the same CTE by adding conditions to the outer SELECT.

Pattern: Threaded Comments

For comment systems with nested replies, a recursive CTE lets you fetch an entire comment thread in the correct display order:

WITH RECURSIVE thread AS (
    -- Top-level comments on a post
    SELECT id, body, user_id, parent_id, created_at,
           0 AS depth,
           CAST(LPAD(id, 10, '0') AS CHAR(1000)) AS sort_path
    FROM comments
    WHERE post_id = :post_id AND parent_id IS NULL

    UNION ALL

    -- Replies
    SELECT c.id, c.body, c.user_id, c.parent_id, c.created_at,
           t.depth + 1,
           CONCAT(t.sort_path, '.', LPAD(c.id, 10, '0'))
    FROM comments c
    INNER JOIN thread t ON c.parent_id = t.id
)
SELECT * FROM thread ORDER BY sort_path;

The sort_path trick builds a string like 0000000001.0000000005.0000000012 that sorts comments in threaded order — parent first, then its children, then their children. The depth column tells you how much to indent each comment in your UI.

Performance Considerations

Recursive CTEs are not a silver bullet. Here are the things to watch:

Index the parent column. The recursive join (c.parent_id = t.id) runs once per level of depth. Without an index on parent_id, each iteration is a full table scan. This is the single most important optimization:

ALTER TABLE categories ADD INDEX idx_parent_id (parent_id);

Set a depth limit. If your data could have cycles (even accidentally), add a depth guard to prevent infinite recursion:

WITH RECURSIVE tree AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, t.depth + 1
    FROM categories c
    INNER JOIN tree t ON c.parent_id = t.id
    WHERE t.depth < 50  -- Safety limit
)
SELECT * FROM tree;

MySQL also has a system variable cte_max_recursion_depth (default 1000) that acts as a global safety net.

Know when to denormalize. If you’re querying the same hierarchy thousands of times per second and the tree rarely changes, consider caching the materialized path or using a closure table alongside the adjacency list. Recursive CTEs are perfect for moderate query volumes and trees that change frequently — when the simplicity of the adjacency list model matters more than raw read speed.

Wrapping It Into a Laravel Scope

If you use this pattern often, wrap it in a query scope or a trait on your Eloquent model:

// app/Models/Category.php
public function scopeWithAncestors(Builder $query, int $categoryId): Builder
{
    return $query->fromSub(function ($sub) use ($categoryId) {
        $sub->selectRaw("
            WITH RECURSIVE ancestors AS (
                SELECT * FROM categories WHERE id = ?
                UNION ALL
                SELECT c.* FROM categories c
                INNER JOIN ancestors a ON c.id = a.parent_id
            )
            SELECT * FROM ancestors
        ", [$categoryId]);
    }, 'categories');
}

Or for maximum clarity, just use DB::select() in a dedicated repository method. Not everything needs to go through Eloquent’s query builder, and raw SQL is perfectly fine when it makes the intent clearer.

Why This Matters Now

The adjacency list model (parent_id on each row) is the most intuitive way to model hierarchical data, and it’s what most developers reach for first. The historical problem was that SQL couldn’t query it efficiently — which is why alternatives like nested sets and materialized paths became popular despite their complexity. Recursive CTEs remove that limitation entirely. You get the simple data model and the powerful queries.

If you’re running MySQL 8.0 or later — and by now, you almost certainly are — there’s no reason to avoid recursive CTEs. They’re standard SQL, they’re well-optimized in MySQL’s query engine, and they turn multi-query tree traversals into single, readable statements.

Sources: