/laravel-cte

Laravel queries with common table expressions

Primary LanguagePHPMIT LicenseMIT

CI Code Coverage Scrutinizer Code Quality Latest Stable Version Total Downloads License

Introduction

This Laravel extension adds support for common table expressions (CTE) to the query builder and Eloquent.

Supports Laravel 5.5+.

Compatibility

  • MySQL 8.0+
  • MariaDB 10.2+
  • PostgreSQL 9.4+
  • SQLite 3.8.3+
  • SQL Server 2008+

Installation

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

Use this command if you are in PowerShell on Windows (e.g. in VS Code):

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

Usage

SELECT Queries

Use withExpression() and provide a query builder instance, an SQL string or a closure:

$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();

Recursive Expressions

Use withRecursiveExpression() for recursive expressions:

$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();

Materialized Expressions

Use withMaterializedExpression()/withNonMaterializedExpression() for (non-)materialized expressions (PostgreSQL, SQLite):

$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();

Custom Columns

You can provide the expression's columns as the third argument:

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

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

Cycle Detection

MariaDB 10.5.2+ and PostgreSQL 14+ support native cycle detection to prevent infinite loops in recursive expressions. Provide the column(s) that indicate(s) a cycle as the third argument to withRecursiveExpressionAndCycleDetection():

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

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

On PostgreSQL, you can customize the name of the column that shows whether a cycle has been detected and the name of the column that tracks the path:

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

INSERT/UPDATE/DELETE Queries

You can use common table expressions in INSERT(Laravel 5.7.17+), UPDATE and DELETE queries:

DB::table('profiles')
    ->withExpression('u', DB::table('users')->select('id', 'name'))
    ->insertUsing(['user_id', 'name'], DB::table('u'));
DB::table('profiles')
    ->withExpression('u', DB::table('users'))
    ->join('u', 'u.id', '=', 'profiles.user_id')
    ->update(['profiles.name' => DB::raw('u.name')]);
DB::table('profiles')
    ->withExpression('u', DB::table('users')->where('active', false))
    ->whereIn('user_id', DB::table('u')->select('id'))
    ->delete();

Eloquent

You can use common table expressions in Eloquent queries.

In Laravel 5.5–5.7, this requires the QueriesExpressions trait:

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();

Recursive Relationships

If you want to implement recursive relationships, you can use this package: staudenmeir/laravel-adjacency-list

Package Conflicts

Lumen

If you are using Lumen, you have to instantiate the query builder manually:

$builder = new \Staudenmeir\LaravelCte\Query\Builder(app('db')->connection());

$result = $builder->from(...)->withExpression(...)->get();

In Eloquent, the QueriesExpressions trait is required for all versions of Lumen.

Contributing

Please see CONTRIBUTING and CODE OF CONDUCT for details.