README

👁 CI
👁 Code Coverage
👁 PHPStan
👁 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+

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
13.x	1.13
12.x	1.12
11.x	1.11
10.x	1.9
9.x	1.6
8.x	1.4
7.x	1.3
6.x	1.2
5.8	1.1
5.5–5.7	1.0

Usage

• SELECT Queries
  • Recursive Expressions
  • Materialized Expressions
  • Custom Columns
  • Cycle Detection
• INSERT/UPDATE/DELETE Queries
• Eloquent
  • Recursive Relationships
• Lumen

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 need 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.

Oracle

If you are using Oracle, you need to instantiate the query builder manually:

$builder = new \Staudenmeir\LaravelCte\Query\OracleBuilder(DB::connection());

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

Contributing

Please see CONTRIBUTING and CODE OF CONDUCT for details.