Recursive Queries in MySQL 8 with Laravel: CTEs, Hierarchies, and Adjacency Lists

Why Recursive CTEs Matter for Laravel Apps

Adjacency-list tables — categories, org charts, threaded comments — are everywhere. The naive fix is loading every row and building the tree in PHP. At a few thousand rows that is fine; at hundreds of thousands it is a silent killer.

MySQL 8.0 shipped recursive common table expressions (CTEs). Laravel's query builder does not expose a dedicated withRecursive() method, but a small amount of raw SQL inside DB::statement or fromRaw gets you there cleanly.

The Schema

CREATE TABLE categories (
    id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    parent_id   BIGINT UNSIGNED NULL REFERENCES categories(id),
    name        VARCHAR(255) NOT NULL,
    slug        VARCHAR(255) NOT NULL
);

CREATE INDEX idx_categories_parent ON categories (parent_id);

Every row points to its parent. Root nodes have parent_id = NULL.

Writing the Recursive CTE

A recursive CTE has two parts separated by UNION ALL: the anchor (the starting row) and the recursive member (the self-join that walks the tree).

WITH RECURSIVE category_tree AS (
    -- anchor: start at the chosen root
    SELECT id, parent_id, name, slug, 0 AS depth
    FROM   categories
    WHERE  id = ?

    UNION ALL

    -- recursive member: join children onto the previous level
    SELECT c.id, c.parent_id, c.name, c.slug, ct.depth + 1
    FROM   categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

MySQL enforces a default recursion depth of 1 000 (cte_max_recursion_depth). For pathological trees you can raise it per-session, but 1 000 levels is already a data-modelling problem.

Calling It from Laravel

The cleanest approach is DB::select with bound parameters:

use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

function categorySubtree(int $rootId): Collection
{
    $sql = <<<SQL
        WITH RECURSIVE category_tree AS (
            SELECT id, parent_id, name, slug, 0 AS depth
            FROM   categories
            WHERE  id = ?
            UNION ALL
            SELECT c.id, c.parent_id, c.name, c.slug, ct.depth + 1
            FROM   categories c
            INNER JOIN category_tree ct ON c.parent_id = ct.id
        )
        SELECT * FROM category_tree ORDER BY depth, name
    SQL;

    return collect(DB::select($sql, [$rootId]));
}

This returns a flat Collection of stdClass objects. Map them to a DTO or Eloquent model as needed.

Hydrating Eloquent Models

If you need full Eloquent models (observers, casts, relations), use fromSub with a raw expression:

use App\Models\Category;
use Illuminate\Support\Facades\DB;

$cte = DB::raw(
    "(WITH RECURSIVE category_tree AS (
        SELECT * FROM categories WHERE id = {$rootId}
        UNION ALL
        SELECT c.* FROM categories c
        INNER JOIN category_tree ct ON c.parent_id = ct.id
    ) SELECT * FROM category_tree) AS category_tree"
);

$models = Category::from($cte)->orderBy('name')->get();

Security note: Never interpolate user input directly. Validate $rootId as an integer before embedding it, or use a prepared statement via DB::select.

Finding All Ancestors (Upward Walk)

Flip the join direction to walk up the tree — useful for breadcrumb generation:

function categoryAncestors(int $leafId): Collection
{
    $sql = <<<SQL
        WITH RECURSIVE ancestors AS (
            SELECT id, parent_id, name, slug, 0 AS depth
            FROM   categories
            WHERE  id = ?
            UNION ALL
            SELECT c.id, c.parent_id, c.name, c.slug, a.depth + 1
            FROM   categories c
            INNER JOIN ancestors a ON c.id = a.parent_id
        )
        SELECT * FROM ancestors ORDER BY depth DESC
    SQL;

    return collect(DB::select($sql, [$leafId]));
}

The result is ordered from root to leaf — ready to render a breadcrumb without any PHP sorting.

Macro for Reuse

If recursive CTEs appear in multiple places, register a query builder macro in a service provider:

use Illuminate\Database\Query\Builder;

Builder::macro('withRecursive', function (string $name, string $sql, array $bindings = []): Builder {
    /** @var Builder $this */
    $this->beforeQuery(function () use ($name, $sql, $bindings) {
        // prepend the CTE — works for DB::table() chains
    });
    // Simpler: just expose a static helper that wraps DB::select
    return $this;
});

In practice, a plain static helper or an Action class is cleaner than a macro here because the CTE must precede the entire statement — it is not composable the same way a WHERE clause is.

Key Takeaways

• MySQL 8 recursive CTEs eliminate the need to load entire adjacency-list tables into PHP.
• Use DB::select with positional bindings for safe, readable recursive queries.
• Hydrate Eloquent models via Model::from(DB::raw(...)) when you need casts and relations.
• Flip the recursive join direction to walk ancestors instead of descendants.
• Validate and cast any user-supplied IDs before embedding them in raw SQL fragments.
• Keep recursion depth in mind; MySQL defaults to 1 000 levels.