PostgreSQL CTEs & Lateral Joins in Laravel | Mohamed Said [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://msaied.com) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ ](https://github.com/EG-Mohamed) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ home ](https://msaied.com) [ articles ](https://msaied.com/articles) PostgreSQL CTEs, Recursive Queries, and Lateral Joins in Laravel 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](https://cdn.msaied.com/241/32858f9c67eae0649999c32a6d31818f.png) #laravel #postgresql #query-builder #performance PostgreSQL CTEs, Recursive Queries, and Lateral Joins in Laravel ================================================================== 19 Jun 2026 3 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 PostgreSQL CTEs, Recursive Queries, and Lateral Joins in Laravel ](#postgresql-ctes-recursive-queries-and-lateral-joins-in-laravel) 2. [ 02 Common Table Expressions (CTEs) with withExpression ](#common-table-expressions-ctes-with-codewithexpressioncode) 3. [ 03 Recursive CTEs for Tree Traversal ](#recursive-ctes-for-tree-traversal) 4. [ 04 LATERAL Joins for "Top N per Group" ](#lateral-joins-for-quottop-n-per-groupquot) 5. [ 05 Keeping It Testable ](#keeping-it-testable) 6. [ 06 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. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-ctes-recursive-queries-and-lateral-joins-in-laravel&text=PostgreSQL+CTEs%2C+Recursive+Queries%2C+and+Lateral+Joins+in+Laravel) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-ctes-recursive-queries-and-lateral-joins-in-laravel) Frequently Asked Questions ---------------------------- 3 questions Q01 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. Q02 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. Q03 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. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Laravel AI SDK: Tool-Calling Agents and Conversation Persistence](https://cdn.msaied.com/260/8c84f424e42da01993c9ba4b8eb19655.png) laravel ai agents ### Laravel AI SDK: Tool-Calling Agents and Conversation Persistence Build reliable tool-calling AI agents in Laravel using the Prism package. Learn how to wire tools, persist con... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 21 Jun 2026 3 min read Read ](https://msaied.com/articles/laravel-ai-sdk-tool-calling-agents-and-conversation-persistence) [ ![Laravel Livewire v3 Internals: Morph Markers, JS Hooks, and Alpine Integration](https://cdn.msaied.com/259/e8ce445f021c2b26ebe4dd5da50014f8.png) livewire laravel alpine ### Laravel Livewire v3 Internals: Morph Markers, JS Hooks, and Alpine Integration Go beyond the docs: understand how Livewire v3 diffs the DOM with morph markers, intercept the lifecycle with... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 21 Jun 2026 3 min read Read ](https://msaied.com/articles/laravel-livewire-v3-internals-morph-markers-js-hooks-and-alpine-integration) [ ![Laravel Package Development: Service Providers, Auto-Discovery, and Config Merging](https://cdn.msaied.com/258/673a80fa8e42ae375a4bba21bdcd92ea.png) laravel packages service-providers ### Laravel Package Development: Service Providers, Auto-Discovery, and Config Merging Build a production-ready Laravel package from scratch — covering service provider design, auto-discovery via c... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 21 Jun 2026 3 min read Read ](https://msaied.com/articles/laravel-package-development-service-providers-auto-discovery-and-config-merging-1) [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://msaied.com)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. Explore - [Home](https://msaied.com) - [Projects](https://msaied.com/projects) - [Articles](https://msaied.com/articles) - [Certificates](https://msaied.com/certificates) - [Contact](https://msaied.com#contact-section) Connect - [ hello@msaied.com ](mailto:hello@msaied.com) - [ +20 109 461 9204 ](tel:+201094619204) © 2026 Mohamed Said. All rights reserved. [ ](https://github.com/EG-Mohamed) [ ](https://www.linkedin.com/in/msaiedm/) [ ](https://wa.me/201094619204) [ ](mailto:hello@msaied.com) [ ](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view)