PHPackages augusl/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. augusl/laravel-cte ActiveLibrary[Database & ORM](/categories/database) augusl/laravel-cte ================== Laravel queries with common table expressions v1.12.6(6mo ago)011MITPHPPHP ^8.2 Since Nov 7Pushed 6mo agoCompare [ Source](https://github.com/augusl/laravel-cte)[ Packagist](https://packagist.org/packages/augusl/laravel-cte)[ Fund](https://paypal.me/JonasStaudenmeir)[ RSS](/packages/augusl-laravel-cte/feed)WikiDiscussions main Synced 1mo ago READMEChangelog (2)Dependencies (8)Versions (3)Used By (0) Laravel CTE =========== [](#laravel-cte) [![CI](https://github.com/staudenmeir/laravel-cte/actions/workflows/ci.yml/badge.svg)](https://github.com/staudenmeir/laravel-cte/actions/workflows/ci.yml?query=branch%3Amain)[![Code Coverage](https://camo.githubusercontent.com/12f185042ce4e78028db98bc91a113f5dbb954adc2f6ba752c81ec27e37d2709/68747470733a2f2f636f6465636f762e696f2f67682f7374617564656e6d6569722f6c61726176656c2d6374652f67726170682f62616467652e7376673f746f6b656e3d4a57484f4f4559594747)](https://codecov.io/gh/staudenmeir/laravel-cte)[![PHPStan](https://camo.githubusercontent.com/2b1732baa25914ee5ccbeaf42980d671de29700b49e0639e1edc8e66181f6905/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f5048505374616e2d6c6576656c25323031302d627269676874677265656e2e7376673f7374796c653d666c6174)](https://github.com/staudenmeir/laravel-cte/actions/workflows/static-analysis.yml?query=branch%3Amain)[![Latest Stable Version](https://camo.githubusercontent.com/19cc3093fe42c6e92c08d3d32ebc9a5713c10744d9aff9ff04de3ff1b4f3cde2/68747470733a2f2f706f7365722e707567782e6f72672f7374617564656e6d6569722f6c61726176656c2d6374652f762f737461626c65)](https://packagist.org/packages/staudenmeir/laravel-cte)[![Total Downloads](https://camo.githubusercontent.com/56c406cdb3b04e2cab93a440e285a0f56b6b8324d84688805f2d7caebd8b0ca5/68747470733a2f2f706f7365722e707567782e6f72672f7374617564656e6d6569722f6c61726176656c2d6374652f646f776e6c6f616473)](https://packagist.org/packages/staudenmeir/laravel-cte/stats)[![License](https://camo.githubusercontent.com/7f44d243de55180602c1742a83e78158290bf7cedfa38b6648915348c26162b8/68747470733a2f2f706f7365722e707567782e6f72672f7374617564656e6d6569722f6c61726176656c2d6374652f6c6963656e7365)](https://github.com/staudenmeir/laravel-cte/blob/main/LICENSE) 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 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 -------- [](#versions) LaravelPackage11.x1.1110.x1.99.x1.68.x1.47.x1.36.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 \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 [](#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 \Staudenmeir\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 35 — LowBetter than 80% of packages Maintenance68 Regular maintenance activity Popularity5 Limited adoption so far Community14 Small or concentrated contributor base Maturity48 Maturing project, gaining track record Bus Factor1 Top contributor holds 85.3% 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 ~0 days Total 2 Last Release 185d ago ### Community Maintainers ![](https://www.gravatar.com/avatar/47f026fa940f93fa71b9e19e0b575293365824649f2d4dd15a429179721b237e?d=identicon)[augusl](/maintainers/augusl) --- Top Contributors [![staudenmeir](https://avatars.githubusercontent.com/u/1853169?v=4)](https://github.com/staudenmeir "staudenmeir (133 commits)")[![chris-doehring](https://avatars.githubusercontent.com/u/6341536?v=4)](https://github.com/chris-doehring "chris-doehring (11 commits)")[![augusl](https://avatars.githubusercontent.com/u/25142251?v=4)](https://github.com/augusl "augusl (3 commits)")[![riesjart](https://avatars.githubusercontent.com/u/23455176?v=4)](https://github.com/riesjart "riesjart (2 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)")[![vixducis](https://avatars.githubusercontent.com/u/378998?v=4)](https://github.com/vixducis "vixducis (1 commits)")[![AlanRezende](https://avatars.githubusercontent.com/u/7322595?v=4)](https://github.com/AlanRezende "AlanRezende (1 commits)")[![f-liva](https://avatars.githubusercontent.com/u/346224?v=4)](https://github.com/f-liva "f-liva (1 commits)")[![Lundis](https://avatars.githubusercontent.com/u/3984143?v=4)](https://github.com/Lundis "Lundis (1 commits)") ### Code Quality TestsPHPUnit Static AnalysisPHPStan Type Coverage Yes ### Embed Badge ![Health badge](/badges/augusl-laravel-cte/health.svg) ``` [![Health](https://phpackages.com/badges/augusl-laravel-cte/health.svg)](https://phpackages.com/packages/augusl-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)