PHPackages marcha/laravel-cte - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. marcha/laravel-cte ActiveLibrary[Database & ORM](/categories/database) marcha/laravel-cte ================== Laravel queries with common table expressions 1.1.0(4mo ago)011MITPHPPHP ^8.2 Since Sep 11Pushed 4mo agoCompare [ Source](https://github.com/marcha/laravel-cte)[ Packagist](https://packagist.org/packages/marcha/laravel-cte)[ Fund](https://paypal.me/JonasStaudenmeir)[ RSS](/packages/marcha-laravel-cte/feed)WikiDiscussions main Synced 1mo ago READMEChangelog (2)Dependencies (8)Versions (3)Used By (0) Laravel CTE =========== [](#laravel-cte) This Laravel extension adds support for common table expressions (CTE) to the query builder and Eloquent. Supports Laravel 5.5+. Compatibility ------------- [](#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 ------------ [](#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 -------- [](#versions) LaravelPackage11.x1.1110.x1.99.x1.68.x1.57.x1.46.x1.25.81.15.5–5.71.0Usage ----- [](#usage) - [SELECT Queries](#select-queries) - [Recursive Expressions](#recursive-expressions) - [Materialized Expressions](#materialized-expressions) - [Custom Columns](#custom-columns) - [Cycle Detection](#cycle-detection) - [INSERT/UPDATE/DELETE Queries](#insertupdatedelete-queries) - [Eloquent](#eloquent) - [Recursive Relationships](#recursive-relationships) - [Lumen](#lumen) ### SELECT Queries [](#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 [](#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 [](#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 [](#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 [](#cycle-detection) [MariaDB 10.5.2+](https://mariadb.com/kb/en/with/#cycle-restrict)and [PostgreSQL 14+](https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CYCLE) 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 [](#insertupdatedelete-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 [](#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 [](#recursive-relationships) If you want to implement recursive relationships, you can use this package: [staudenmeir/laravel-adjacency-list](https://github.com/staudenmeir/laravel-adjacency-list) ### Lumen [](#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 ------------ [](#contributing) Please see [CONTRIBUTING](.github/CONTRIBUTING.md) and [CODE OF CONDUCT](.github/CODE_OF_CONDUCT.md) for details. ### Health Score 38 — LowBetter than 85% of packages Maintenance74 Regular maintenance activity Popularity5 Limited adoption so far Community14 Small or concentrated contributor base Maturity53 Maturing project, gaining track record Bus Factor1 Top contributor holds 83.2% of commits — single point of failure How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window. **Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores. **Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement. **Maturity (30%)** — Project age, version count, PHP version support, and release stability. ### Release Activity Cadence Every ~457 days Total 2 Last Release 149d ago ### Community Maintainers ![](https://www.gravatar.com/avatar/b28acee7c882f6750786ee19a357ae7fb652f50d583d5e8adaee65e336f73717?d=identicon)[marcha](/maintainers/marcha) --- Top Contributors [![staudenmeir](https://avatars.githubusercontent.com/u/1853169?v=4)](https://github.com/staudenmeir "staudenmeir (119 commits)")[![chris-doehring](https://avatars.githubusercontent.com/u/6341536?v=4)](https://github.com/chris-doehring "chris-doehring (11 commits)")[![gh-bane](https://avatars.githubusercontent.com/u/5020185?v=4)](https://github.com/gh-bane "gh-bane (4 commits)")[![riesjart](https://avatars.githubusercontent.com/u/23455176?v=4)](https://github.com/riesjart "riesjart (2 commits)")[![marcha](https://avatars.githubusercontent.com/u/3828422?v=4)](https://github.com/marcha "marcha (2 commits)")[![vixducis](https://avatars.githubusercontent.com/u/378998?v=4)](https://github.com/vixducis "vixducis (1 commits)")[![Lundis](https://avatars.githubusercontent.com/u/3984143?v=4)](https://github.com/Lundis "Lundis (1 commits)")[![Treggats](https://avatars.githubusercontent.com/u/27585?v=4)](https://github.com/Treggats "Treggats (1 commits)")[![usermp](https://avatars.githubusercontent.com/u/39251365?v=4)](https://github.com/usermp "usermp (1 commits)")[![AdalbertMemSQL](https://avatars.githubusercontent.com/u/55380838?v=4)](https://github.com/AdalbertMemSQL "AdalbertMemSQL (1 commits)") ### Code Quality TestsPHPUnit Static AnalysisPHPStan Type Coverage Yes ### Embed Badge ![Health badge](/badges/marcha-laravel-cte/health.svg) ``` [![Health](https://phpackages.com/badges/marcha-laravel-cte/health.svg)](https://phpackages.com/packages/marcha-laravel-cte) ``` ### Alternatives [owen-it/laravel-auditing Audit changes of your Eloquent models in Laravel 3.4k33.0M95](/packages/owen-it-laravel-auditing)[staudenmeir/eloquent-json-relations Laravel Eloquent relationships with JSON keys 1.1k5.8M24](/packages/staudenmeir-eloquent-json-relations)[bavix/laravel-wallet It's easy to work with a virtual wallet. 1.3k1.1M11](/packages/bavix-laravel-wallet)[dragon-code/migrate-db Easy data transfer from one database to another 15717.4k](/packages/dragon-code-migrate-db)[gearbox-solutions/eloquent-filemaker A package for getting FileMaker records as Eloquent models in Laravel 6454.8k2](/packages/gearbox-solutions-eloquent-filemaker)[cybercog/laravel-ownership Laravel Ownership simplify management of Eloquent model's owner. 9126.6k3](/packages/cybercog-laravel-ownership) PHPackages © 2026 [Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)