marcha / laravel-cte
Laravel queries with common table expressions
Fund package maintenance!
paypal.me/JonasStaudenmeir
Requires
- php: ^8.2
- illuminate/database: ^11.0
Requires (Dev)
- harrygulliford/laravel-firebird: dev-laravel-11.x
- larastan/larastan: ^2.9
- orchestra/testbench: ^9.0
- phpstan/phpstan: ^1.10
- phpunit/phpunit: ^11.0
- singlestoredb/singlestoredb-laravel: ^1.5.4
- yajra/laravel-oci8: ^11.2.4
README
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 marcha/laravel-cte:"^1.0"
Use this command if you are in PowerShell on Windows (e.g. in VS Code):
composer require marcha/laravel-cte:"^^^^1.0"
Versions
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 \Marcha\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 \Marcha\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.