Skip to content

Laravel queries with common table expressions

License

Notifications You must be signed in to change notification settings

siberfx/laravel-cte

 
 

Repository files navigation

Laravel CTE

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

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+
  • Oracle 9.2+
  • SingleStore 8.1+
  • Firebird

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"

Versions

Laravel Package
11.x 1.11
10.x 1.9
9.x 1.6
8.x 1.5
7.x 1.4
6.x 1.2
5.8 1.1
5.5–5.7 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, 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

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.

About

Laravel queries with common table expressions

Resources

License

Code of conduct

Stars

Watchers

Forks

Packages

No packages published

Languages

  • PHP 100.0%