Laravel CTE

Install the package via Composer. For standard environments, use the standard requirement command. If you are using PowerShell on Windows (e.g., in VS Code), use the version with four carets to avoid parsing issues.

# Standard installation
composer require staudenmeir/laravel-cte:"^1.0"

# PowerShell/Windows installation
composer require staudenmeir/laravel-cte:"^^^^1.0"

To use CTEs with Eloquent models, you must use the QueriesExpressions trait. This is required for Laravel 5.5–5.7. For newer versions, the trait is still used to enable these capabilities on the model.

class User extends Model
{
    use \Staudenmeir\LaravelCte\Eloquent\QueriesExpressions;
}

$query = User::whereNull('parent_id')
    ->unionAll(
        User::select('users.*')
            ->join('tree', 'tree.id', '=', 'users.parent_id')
    );

$tree = User::from('tree')
    ->withRecursiveExpression('tree', $query)
    ->get();

When using Oracle, you must manually instantiate the OracleBuilder to access CTE functionality.

$builder = new \Staudenmeir\LaravelCte\Query\OracleBuilder(DB::connection());
$result = $builder->from(...)->withExpression(...)->get();

MariaDB 10.5.2+ and PostgreSQL 14+ support native cycle detection. Use withRecursiveExpressionAndCycleDetection() and provide the column(s) that indicate a cycle as the third argument.

On PostgreSQL, you can also specify the name of the cycle detection column and the path tracking column as the fourth and fifth arguments.

// Basic cycle detection
$tree = DB::table('tree')
    ->withRecursiveExpressionAndCycleDetection('tree', $query, 'id')
    ->get();

// PostgreSQL specific customization
$tree = DB::table('tree')
    ->withRecursiveExpressionAndCycleDetection('tree', $query, 'id', 'is_cycle', 'path')
    ->get();

For PostgreSQL and SQLite, you can explicitly control whether an expression is materialized using withMaterializedExpression() or withNonMaterializedExpression().

$posts = DB::table('p')
    ->select('p.*', 'u.name')
    ->withMaterializedExpression('p', DB::table('posts'))
    ->withNonMaterializedExpression('u', function ($query) {
        $query->from('users');
    })
    ->join('u', 'u.id', '=', 'p.user_id')
    ->get();

In Lumen, you must manually instantiate the query builder to use CTEs. If using Eloquent in Lumen, the QueriesExpressions trait is required for all versions.

// Manual Query Builder
$builder = new \Staudenmeir\LaravelCte\Query\Builder(app('db')->connection());
$result = $builder->from(...)->withExpression(...)->get();

For recursive queries, use withRecursiveExpression(). This is typically used by defining a query that performs a unionAll between a base case and a recursive case that joins back to the CTE name.

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

$tree = DB::table('tree')
    ->withRecursiveExpression('tree', $query)
    ->get();

When using a raw SQL string for an expression, you can provide the column names as the third argument to withRecursiveExpression().

$query = 'select 1 union all select number + 1 from numbers where number < 10';

$numbers = DB::table('numbers')
    ->withRecursiveExpression('numbers', $query, ['number'])
    ->get();

Common Table Expressions can be used to drive data modification queries.

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

// UPDATE
DB::table('profiles')
    ->withExpression('u', DB::table('users'))
    ->join('u', 'u.id', '=', 'profiles.user_id')
    ->update(['profiles.name' => DB::raw('u.name')]);

// DELETE
DB::table('profiles')
    ->withExpression('u', DB::table('users')->where('active', false))
    ->whereIn('user_id', DB::table('u')->select('id'))
    ->delete();

You can add Common Table Expressions to your SELECT queries using withExpression(). This method accepts a name for the expression, and a query builder instance, an SQL string, or a closure as the second argument.

$posts = DB::table('p')
    ->select('p.*', 'u.name')
    ->withExpression('p', DB::table('posts'))
    ->withExpression('u', function ($query) {
        $query->from('users');
    })
    ->join('u', 'u.id', '=', 'p.user_id')
    ->get();