Laravel Cte Laravel Package

Getting Started

Install the version that matches your Laravel release, not just the latest tag. The version matrix is strict:

composer require staudenmeir/laravel-cte:"^1.13"

On Windows PowerShell the caret is a special character, so quoting breaks — use the doubled form the README shows ("^^^^1.13") or run the command from Git Bash/WSL.

There is no config file, no migration, no published assets, and nothing to register on Laravel — the service provider is auto-discovered and swaps in a connection/query-builder layer. That also means there is nothing to uninstall beyond composer remove; the dependency is purely additive at the SQL-generation level.

Where to look first. Read the README's "SELECT Queries" section end-to-end (it is the whole API surface), then tests/ in the repo — the test suite is the de-facto spec for how each grammar (MySQL, Postgres, SQLite, SQL Server, Oracle) renders WITH, and it is faster than reverse-engineering toSql() output. Everything else is Eloquent sugar over the same builder methods.

First use case: a category tree. The canonical day-one win is replacing a recursive PHP loop or an N+1 chain with one query:

$seed = DB::table('categories')->whereNull('parent_id');

$query = $seed->unionAll(
    DB::table('categories')
        ->select('categories.*')
        ->join('tree', 'tree.id', '=', 'categories.parent_id')
);

$tree = DB::table('tree')
    ->withRecursiveExpression('tree', $query, ['id', 'parent_id', 'name'])
    ->get();

Two things to notice immediately, because they trip up every first attempt: the recursive member must reference the CTE name (tree) as if it were a real table, and the outer query must from('tree') — not from('categories'). The CTE is the result set; the base table only appears inside the expression.

Implementation Patterns

Chain multiple expressions instead of nesting subqueries. Named expressions read linearly and are the closest thing Laravel has to a WITH clause:

$activeUsers = DB::table('users')->where('active', true);

$rows = DB::table('p')
    ->select('p.*', 'u.name', 's.total')
    ->withExpression('u', $activeUsers)
    ->withExpression('s', DB::table('sales')
        ->selectRaw('user_id, sum(amount) as total')
        ->groupBy('user_id'))
    ->join('u', 'u.id', '=', 'p.user_id')
    ->leftJoin('s', 's.user_id', '=', 'u.id')
    ->get();

Declare them in dependency order — an expression can only reference expressions declared before it.

Pass closures, not strings, when anything is user-derived. A string expression is inlined verbatim with no bindings; a closure/builder keeps parameter binding intact and stays safe under DB::listen and query logging:

->withExpression('recent', function ($q) use ($since) {
    $q->from('orders')->where('created_at', '>=', $since);
})

Add depth and path columns to recursive CTEs. Recursion plus selectRaw on the recursive member is the cheapest way to get sortable hierarchy output in one round trip:

$query = DB::table('comments')
    ->select('id', 'parent_id', 'body')
    ->selectRaw('0 as depth')
    ->whereNull('parent_id')
    ->unionAll(
        DB::table('comments')
            ->join('thread', 'thread.id', '=', 'comments.parent_id')
            ->select('comments.id', 'comments.parent_id', 'comments.body')
            ->selectRaw('thread.depth + 1')
    );

Because the recursive member now has a different column list, the third argument on withRecursiveExpression('thread', $query, [...]) is mandatory on Postgres and SQL Server.

Use explicit cycle detection where supported. MariaDB 10.5.2+ and PostgreSQL 14+ have native CYCLE support; elsewhere the column arguments are silently ignored, so never rely on them as your only termination guarantee:

$tree = DB::table('tree')
    ->withRecursiveExpressionAndCycleDetection('tree', $query, 'id', 'is_cycle’, 'path')
    ->get();

On Postgres you can rename the two generated columns (4th/5th args) to keep your API stable. On MySQL, SQL Server and SQLite you must terminate in the recursive predicate yourself and add a server-side guard (SET SESSION max_execution_time, OPTION (MAXRECURSION n) via DB::raw, or statement_timeout).

Reuse the same CTE in multiple statements with insertUsing. This is the feature most teams under-use — it is a server-side INSERT … SELECT with a WITH clause, not an application-side loop:

DB::table('profiles')
    ->withExpression('u', DB::table('users')->select('id', 'name'))
    ->insertUsing(['user_id', 'name'], DB::table('u'));

Same for archiving or cascading deletes: derive the ID set once as an expression, then whereIn('user_id', DB::table('u')->select('id'))->delete(). Since v1.5.4 insertUsing() returns the affected row count, so it slots into a job's idempotency check.

Wrap recurring traversals in a query-object, not a controller. Because the builder is fluent and expressions are just builder instances, the sustainable pattern is a small class with toQuery() returning the configured builder, then a scope on the model:

class OrgTree
{
    public static function query(int $rootId): Builder
    {
        $seed = DB::table('employees')->where('id', $rootId);
        $recursive = DB::table('employees')
            ->join('org', 'org.id', '=', 'employees.manager_id')
            ->select('employees.*');

        return DB::table('org')
            ->withRecursiveExpression('org', $seed->unionAll($recursive));
    }
}

// usage
$chain = OrgTree::query($managerId)->pluck('name');

In Eloquent, treat the CTE name as the model's table for that query. Model::from('tree')->withRecursiveExpression('tree', $query) reuses your existing scopes and casts on the outer query; the returned rows hydrate as that model. For real recursive relationships (ancestors/descendants, subtree, path) do not hand-roll it — staudenmeir/laravel-adjacency-list is the maintained companion and builds on this package.

Integration tip for reporting endpoints: build the CTE once and reuse it across count/pagination calls, since Laravel's paginate() runs a second count query. If the count query is expensive, cache the ID set from the CTE and paginate against it rather than re-running the traversal.

Gotchas and Tips

Engine minimums are real and the package cannot paper over them. MySQL 8.0+, MariaDB 10.2+, PostgreSQL 9.4+, SQLite 3.8.3+, SQL Server 2008+. Laravel still supports MySQL 5.7 in places; a WITH clause there is a syntax error, and it will only surface in production if CI runs SQLite/MariaDB. Pin a DB version or assert it in a health check.

The base table and the CTE name must not collide. withRecursiveExpression('users', ...) then from('users') resolves to the real table on some grammars and the CTE on others. Always give expressions a distinct alias (tree, org, ancestors).

Expression order is semantic, not cosmetic. withExpression('b', ...) referencing a before a is declared produces a runtime error. Declare leaves first; keep the ordering next to the definition in code review.

Non-recursive UNION inside the recursive member is not portable. Use UNION ALL; if you need duplicate elimination, do it in the outer query. UNION in a recursive CTE is rejected by Postgres and MySQL.

Silent scope leakage in Eloquent. Global scopes, SoftDeletes, and withTrashed() apply to the seed and recursive builders you pass in, not just the outer query. A soft-deleted parent silently truncates the tree. Be deliberate: withoutGlobalScopes() on the recursive member if the hierarchy should survive deletes.

Materialized hints are Postgres/SQLite only. withMaterializedExpression() / withNonMaterializedExpression() throw or no-op elsewhere. They matter when a CTE is referenced more than once (Postgres materializes by default in that case; NOT MATERIALIZED lets it be inlined and use indexes). Do not scatter them — measure first.

toSql() hides bindings; use toRawSql() for real debugging. This package supports toRawSql() since v1.7.1. For recursion problems, DB::listen() plus EXPLAIN (ANALYZE, VERBOSE) / EXPLAIN FORMAT=TREE tells you whether the CTE was materialized or folded — that is almost always the source of a "CTE is slower than my N+1" complaint.

Recursive queries can hang the connection, not just the request. A missing or wrong termination predicate produces an infinite loop and a runaway query. Put a guard in the recursive member (WHERE depth < 20) even when using cycle detection, and add a statement timeout on the connection used for traversal.

Raw strings bypass the binder. withExpression('x', 'select * from users where id = ' . $id) is SQL injection with extra steps. Reserve raw strings for static SQL (number generators, select 1 union all …).

ORDER BY inside an expression is usually dead weight — most engines discard it, and on SQL Server it is an outright error without TOP. Sort the outer query.

Oracle and Lumen need manual wiring. Oracle: instantiate \Staudenmeir\LaravelCte\Query\OracleBuilder instead of relying on the default connection. Lumen: instantiate \Staudenmeir\LaravelCte\Query\Builder(app('db')->connection()), and in Eloquent add the QueriesExpressions trait on every Lumen version (on Laravel it is only needed for 5.5–5.7).

Custom query builders must extend this package's builder. Historical issues (#29) and the grammar-selection fix in v1.12.3 both stem from custom builders/connections resolving the wrong grammar. If you already override Query\Builder or the connection factory, verify withExpression() still emits WITH on every supported connection in a test — this is the single most likely integration break during a Laravel upgrade.

Firebird support was removed in v1.13 (alongside Laravel 13 support). If you are on Firebird, stay on 1.12.x and plan a migration.

Extension points are thin by design. There is no configuration and no event hooks; the only real extension surfaces are (a) subclassing the builder/grammar, (b) composing Builder instances inside your own query objects, and (c) using DB::raw escapes such as OPTION (MAXRECURSION …). Keep traversal SQL in dedicated classes rather than in controllers — with no config layer, those classes are your abstraction.