This Laravel extension adds support for common table expressions (CTE) to the query builder and Eloquent.
Supports Laravel 5.5+.
- MySQL 8.0+
- MariaDB 10.2+
- PostgreSQL 9.4+
- SQLite 3.8.3+
- SQL Server 2008+
composer require paxha/laravel-cte:"^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();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();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();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 \Paxha\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: paxha/laravel-adjacency-list
If you are using Lumen, you have to instantiate the query builder manually:
$builder = new \Paxha\LaravelCte\Query\Builder(app('db')->connection());
$result = $builder->from(...)->withExpression(...)->get();In Eloquent, the QueriesExpressions trait is required for all versions of Lumen.
Please see CONTRIBUTING and CODE OF CONDUCT for details.