PostgreSQL CTEs & Lateral Joins in Laravel | Mohamed Said [Skip to content](#main) [ ![](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed SaidLaravel Backend Engineer ](https://msaied.com/public) - [Home](https://msaied.com/public) - [Projects](https://msaied.com/public/projects) - [Articles](https://msaied.com/public/articles) - [Certificates](https://msaied.com/public/certificates) - [About](https://msaied.com/public#about) [ Contact](https://msaied.com/public#contact) Menu Menu ---- Close - [HomeStart here](https://msaied.com/public) - [ProjectsCase studies](https://msaied.com/public/projects) - [ArticlesEngineering notes](https://msaied.com/public/articles) - [CertificatesCredentials](https://msaied.com/public/certificates) - [AboutHow I work](https://msaied.com/public#about) - [ContactGet in touch](https://msaied.com/public#contact) [Start a conversation](https://msaied.com/public#contact) [WhatsApp](https://wa.me/201094619204) [Email](mailto:hello@msaied.com) 1. [Home](https://msaied.com/public) 2. / 3. [Articles](https://msaied.com/public/articles) 4. / 5. PostgreSQL CTEs, Recursive Queries, and Lateral Joins in Laravel PostgreSQL CTEs, Recursive Queries, and Lateral Joins in Laravel ================================================================= Go beyond basic Eloquent with raw PostgreSQL power: composable CTEs, recursive tree traversal, and LATERAL joins — all wired cleanly into Laravel's query builder. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://msaied.com/public#person) Published 19 Jun 2026 · Updated 19 Jun 2026 · 3 min read ShareCopy linkCopied ![PostgreSQL CTEs, Recursive Queries, and Lateral Joins in Laravel](https://cdn.msaied.com/241/32858f9c67eae0649999c32a6d31818f.png) On this page +1. [PostgreSQL CTEs, Recursive Queries, and Lateral Joins in Laravel](#postgresql-ctes-recursive-queries-and-lateral-joins-in-laravel) 2. [Common Table Expressions (CTEs) with withExpression](#common-table-expressions-ctes-with-codewithexpressioncode) 3. [Recursive CTEs for Tree Traversal](#recursive-ctes-for-tree-traversal) 4. [LATERAL Joins for "Top N per Group"](#lateral-joins-for-quottop-n-per-groupquot) 5. [Keeping It Testable](#keeping-it-testable) 6. [Takeaways](#takeaways) PostgreSQL CTEs, Recursive Queries, and Lateral Joins in Laravel ---------------------------------------------------------------- Eloquent handles 90% of your queries elegantly. The remaining 10% — hierarchical data, ranked results, correlated subqueries — is where raw PostgreSQL features pay dividends. Laravel's query builder gives you enough surface area to use them without abandoning the framework entirely. ### Common Table Expressions (CTEs) with `withExpression` Laravel 8+ ships with `DB::query()->withExpression()` via the `staudenmeir/laravel-cte` package, but for pure PostgreSQL you can also drop into `DB::statement` or use `selectRaw` with a leading `WITH` block. The cleaner approach is the package: ```bash composer require staudenmeir/laravel-cte ``` ```php use Illuminate\Support\Facades\DB; $results = DB::table('orders') ->withExpression('ranked_orders', function ($query) { $query->from('orders') ->select([ 'id', 'user_id', 'total', DB::raw('RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS rnk'), ]); }) ->from('ranked_orders') ->where('rnk', 1) ->get(); ``` This pulls the single highest-value order per user in one round-trip. The CTE keeps the window function isolated, making the outer query readable and the execution plan efficient — PostgreSQL materialises the CTE once. ### Recursive CTEs for Tree Traversal Category trees, org charts, threaded comments — all map naturally to a recursive CTE. Eloquent has no native support, but a raw expression works cleanly: ```php $categoryId = 5; $descendants = DB::select( join( DB::raw('LATERAL ( SELECT id, title, published_at FROM posts WHERE posts.user_id = users.id ORDER BY published_at DESC LIMIT 3 ) AS recent_posts'), DB::raw('TRUE'), '=', DB::raw('TRUE') ) ->select(['users.id AS user_id', 'users.name', 'recent_posts.*']) ->get(); ``` The `ON TRUE` trick is idiomatic for `CROSS JOIN LATERAL` semantics when you want all users regardless of whether they have posts. Switch to `JOIN LATERAL ... ON TRUE` and add `WHERE recent_posts.id IS NOT NULL` to filter users with no posts. ### Keeping It Testable Raw SQL in repositories is fine as long as it's behind an interface. Test with a real PostgreSQL database in your Pest suite — SQLite won't execute `WITH RECURSIVE` or `LATERAL`: ```php uses(RefreshDatabase::class); it('returns descendants in depth order', function () { $root = Category::factory()->create(); $child = Category::factory()->for($root, 'parent')->create(); $results = app(CategoryRepository::class)->descendants($root->id); expect($results)->toHaveCount(2) ->and($results->first()->id)->toBe($root->id); }); ``` Set `DB_CONNECTION=pgsql` in `phpunit.xml` for the test suite and spin up a throwaway Postgres container in CI. ### Takeaways - **CTEs** keep complex subqueries composable and readable; use `staudenmeir/laravel-cte` for builder integration. - **Recursive CTEs** are the idiomatic PostgreSQL solution for hierarchical data — avoid application-side tree walking. - **LATERAL joins** replace multiple queries for "top N per group" patterns with a single, plan-friendly statement. - Raw SQL in repositories is acceptable; hide it behind interfaces and test against a real PostgreSQL instance. - Never assume SQLite parity — advanced PostgreSQL features require a real Postgres environment in CI. - [laravel](https://msaied.com/public/articles?search=laravel) - [postgresql](https://msaied.com/public/articles?search=postgresql) - [query-builder](https://msaied.com/public/articles?search=query-builder) - [performance](https://msaied.com/public/articles?search=performance) Frequently asked questions --------------------------- Can I use CTEs with Eloquent models instead of DB::table?Yes. The `staudenmeir/laravel-cte` package adds `withExpression()` to both the query builder and Eloquent's builder, so you can call `User::query()->withExpression(...)->get()` and still receive hydrated model instances. Will recursive CTEs cause infinite loops on circular parent-child data?PostgreSQL 14+ supports the `CYCLE` clause to detect and break cycles automatically. On older versions, add a depth limit (`WHERE depth < 100`) or a path array check in the recursive member to guard against corrupt data. Is LATERAL join support available in MySQL?MySQL 8.0.14+ supports LATERAL derived tables, but the syntax and optimiser behaviour differ from PostgreSQL. If your application targets both engines, abstract the query behind a repository and provide separate implementations per driver. ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp)About the author ---------------- [Mohamed Said](https://msaied.com/public#person)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. [About](https://msaied.com/public#about) [GitHub ↗](https://github.com/EG-Mohamed) [LinkedIn ↗](https://www.linkedin.com/in/msaiedm/) [WhatsApp ↗](https://wa.me/201094619204) [Email Address ↗](mailto:hello@msaied.com) [My CV ↗](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) [Previous articlePostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection](https://msaied.com/public/articles/postgresql-window-functions-in-laravel-ranking-running-totals-and-gap-detection) [Next articleTesting Filament Resources, Actions, and Form Assertions with Pest](https://msaied.com/public/articles/testing-filament-resources-actions-and-form-assertions-with-pest) On this page ------------- 1. [PostgreSQL CTEs, Recursive Queries, and Lateral Joins in Laravel](#postgresql-ctes-recursive-queries-and-lateral-joins-in-laravel) 2. [Common Table Expressions (CTEs) with withExpression](#common-table-expressions-ctes-with-codewithexpressioncode) 3. [Recursive CTEs for Tree Traversal](#recursive-ctes-for-tree-traversal) 4. [LATERAL Joins for "Top N per Group"](#lateral-joins-for-quottop-n-per-groupquot) 5. [Keeping It Testable](#keeping-it-testable) 6. [Takeaways](#takeaways) ### Have a technical challenge? Tell me what you’re building. I reply within two working days. [Start a conversation](https://msaied.com/public#contact) Related articles ----------------- [ ![](https://cdn.msaied.com/745/8744e1be5136b430da52e9fca3ed3964.png) · 3 min read### Service Container Deep Dive: Contextual Binding, Tagging, and Method Injection 6 Oct 2026 ](https://msaied.com/public/articles/service-container-deep-dive-contextual-binding-tagging-and-method-injection-1) [ ![](https://cdn.msaied.com/743/8998fac3a41451ab3fe1588194e17a43.png) Filament · 3 min read### Securing Filament Plugins with Plumb: Automated Security Scoring for PHP Packages 5 Oct 2026 ](https://msaied.com/public/articles/securing-filament-plugins-with-plumb-automated-security-scoring-for-php-packages) [ ![](https://cdn.msaied.com/742/2d02018669cdeedccb5de2efb898f0ee.png) Filament · 3 min read### Filament v3.3.56 Released: File Hash Names and Livewire Upload Fix 5 Oct 2026 ](https://msaied.com/public/articles/filament-v3356-released-file-hash-names-and-livewire-upload-fix) Have a technical challenge? ---------------------------- Tell me what you’re building. I reply within two working days. [Discuss your project ↗](https://msaied.com/public#contact) © 2026 Mohamed Said · Built with Laravel, meant to last.Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. - [Home](https://msaied.com/public) - [Articles](https://msaied.com/public/articles) - [Certificates](https://msaied.com/public/certificates) - [GitHub](https://github.com/EG-Mohamed) - [LinkedIn](https://www.linkedin.com/in/msaiedm/) - [WhatsApp](https://wa.me/201094619204) - [Email Address](mailto:hello@msaied.com) - [My CV](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) - [Sitemap](https://msaied.com/public/sitemap.xml)