Laravel queries with common table expressions
This Laravel extension adds support for common table expressions (CTE) to the query builder and Eloquent.
Supports Laravel 5.5+.
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"| 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 |
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();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();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();You can provide the expression's columns as the third argument:
$query = 'select 1 union all select number + 1 from numbers where number withRecursiveExpression('numbers', $query, ['number'])
->get();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();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();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();If you want to implement recursive relationships, you can use this package: staudenmeir/laravel-adjacency-list
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.
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();Please see CONTRIBUTING and CODE OF CONDUCT for details.
No open issues yet, or sync has not completed.