PostgreSQL CTEs & Window Functions 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, Window Functions, and Lateral Joins in Laravel On this page 1. [ Why Reach Beyond Eloquent's Comfort Zone ](#why-reach-beyond-eloquents-comfort-zone) 2. [ Common Table Expressions (CTEs) ](#common-table-expressions-ctes) 3. [ Window Functions for Rankings and Running Totals ](#window-functions-for-rankings-and-running-totals) 4. [ Tip: Avoid Hydrating Eloquent Models Here ](#tip-avoid-hydrating-eloquent-models-here) 5. [ LATERAL Joins: Correlated Subqueries Done Right ](#lateral-joins-correlated-subqueries-done-right) 6. [ Keeping It Testable ](#keeping-it-testable) 7. [ Takeaways ](#takeaways) ![PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel](https://cdn.msaied.com/175/8e5685c14467b502c2bcbd62b4f47f64.png) #laravel #postgresql #eloquent #performance PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel ================================================================= 14 Jun 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said Table of contents 1. [ 01 Why Reach Beyond Eloquent's Comfort Zone ](#why-reach-beyond-eloquents-comfort-zone) 2. [ 02 Common Table Expressions (CTEs) ](#common-table-expressions-ctes) 3. [ 03 Window Functions for Rankings and Running Totals ](#window-functions-for-rankings-and-running-totals) 4. [ 04 Tip: Avoid Hydrating Eloquent Models Here ](#tip-avoid-hydrating-eloquent-models-here) 5. [ 05 LATERAL Joins: Correlated Subqueries Done Right ](#lateral-joins-correlated-subqueries-done-right) 6. [ 06 Keeping It Testable ](#keeping-it-testable) 7. [ 07 Takeaways ](#takeaways) Why Reach Beyond Eloquent's Comfort Zone ---------------------------------------- Eloquent handles 90% of your day-to-day queries elegantly. But when you're building reporting dashboards, ranking rows, or computing running totals, you hit a wall. PostgreSQL has solved these problems for decades with CTEs, window functions, and `LATERAL` joins. Laravel's query builder doesn't abstract them away — it lets you compose them with raw expressions, keeping SQL readable and your PHP clean. --- Common Table Expressions (CTEs) ------------------------------- A CTE names a subquery so you can reference it multiple times or chain logic clearly. Laravel has no first-class `withCte()` method, but `DB::statement` and `fromRaw` give you full control. ```php $results = DB::table( DB::raw('( WITH ranked_orders AS ( SELECT customer_id, total, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn FROM orders ) SELECT customer_id, total FROM ranked_orders WHERE rn = 1 ) AS latest_orders') )->get(); ``` For reuse across the codebase, wrap this in a dedicated query class: ```php final class LatestOrderPerCustomerQuery { public function get(): Collection { return DB::table(DB::raw('( WITH ranked AS ( SELECT customer_id, total, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY created_at DESC ) AS rn FROM orders ) SELECT customer_id, total FROM ranked WHERE rn = 1 ) AS q'))->get(); } } ``` This keeps your controllers thin and the SQL co-located with the intent. --- Window Functions for Rankings and Running Totals ------------------------------------------------ Window functions compute values across a set of rows related to the current row — without collapsing them like `GROUP BY` does. ```php $rows = DB::select(id, customerId: $row->customer_id, total: $row->total, runningTotal: $row->running_total, rank: $row->rank, )); ``` ### Tip: Avoid Hydrating Eloquent Models Here Using `DB::select()` instead of `Model::all()` skips Eloquent hydration entirely — a meaningful win when you're pulling thousands of rows for a report. --- LATERAL Joins: Correlated Subqueries Done Right ----------------------------------------------- A `LATERAL` join lets the right-hand subquery reference columns from the left-hand table — something a plain subquery cannot do. It's ideal for "top N per group" patterns. ```php $results = DB::table('customers') ->joinSub( DB::raw('( SELECT o.customer_id, o.total, o.created_at FROM orders o WHERE o.customer_id = customers.id ORDER BY o.created_at DESC LIMIT 3 )'), 'recent', 'recent.customer_id', '=', 'customers.id' ) ->select('customers.name', 'recent.total', 'recent.created_at') ->get(); ``` Because `joinSub` wraps in parentheses automatically, you need the `LATERAL` keyword explicitly: ```php $results = DB::table('customers') ->join( DB::raw('LATERAL ( SELECT total, created_at FROM orders WHERE orders.customer_id = customers.id ORDER BY created_at DESC LIMIT 3 ) recent'), DB::raw('true'), '=', DB::raw('true') ) ->select('customers.name', 'recent.total', 'recent.created_at') ->get(); ``` The `ON true` trick satisfies Laravel's join signature while letting PostgreSQL handle the correlation. --- Keeping It Testable ------------------- Wrap each complex query in a dedicated class and test it against a real PostgreSQL database (not SQLite) using Pest: ```php it('returns the latest order per customer', function () { Customer::factory()->has(Order::factory()->count(3))->create(); $results = (new LatestOrderPerCustomerQuery)->get(); expect($results)->toHaveCount(1); }); ``` SQLite doesn't support `LATERAL` or many window functions — always run these tests against Postgres in CI. --- Takeaways --------- - **CTEs** name subqueries for readability and reuse; wrap them in dedicated query classes. - **Window functions** rank and aggregate without collapsing rows — far cleaner than multiple queries. - **LATERAL joins** enable correlated subqueries that reference the outer table, perfect for top-N-per-group. - Use `DB::select()` or `DB::table()` with raw expressions; skip Eloquent hydration for pure reporting. - Always test PostgreSQL-specific queries against a real Postgres instance in CI — SQLite will silently miss incompatibilities. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-ctes-window-functions-and-lateral-joins-in-laravel&text=PostgreSQL+CTEs%2C+Window+Functions%2C+and+Lateral+Joins+in+Laravel) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-ctes-window-functions-and-lateral-joins-in-laravel) Frequently Asked Questions ---------------------------- 3 questions Q01 Can I use CTEs with Eloquent models instead of DB::table? Yes. You can call `YourModel::from(DB::raw('(WITH ... ) AS alias'))` to hydrate Eloquent models from a CTE result, but for pure reporting queries the overhead of hydration is rarely worth it. Q02 Will LATERAL joins work with MySQL in the same Laravel codebase? MySQL 8.0+ supports LATERAL joins, but the syntax and optimizer behaviour differ from PostgreSQL. If you target both databases, abstract the query behind an interface and provide separate implementations per driver. Q03 How do I prevent SQL injection when building raw expressions? Always use query builder bindings via `DB::raw()` combined with `->addBinding()`, or pass user input through `DB::table()->where()` clauses rather than string-interpolating it into raw SQL fragments. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Partial Indexes and Covering Indexes in PostgreSQL: A Laravel Developer's Guide](https://cdn.msaied.com/665/ced6904aad758906b6047d70ea25e267.png) postgresql laravel performance ### Partial Indexes and Covering Indexes in PostgreSQL: A Laravel Developer's Guide Learn how partial and covering indexes eliminate wasted index space and redundant heap fetches in Laravel apps... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 13 Sep 2026 4 min read Read ](https://msaied.com/articles/partial-indexes-and-covering-indexes-in-postgresql-a-laravel-developers-guide-1) [ ![Filament v4 at Scale: Multi-Panel Auth, Custom Panels, and Table Query Tuning](https://cdn.msaied.com/664/be327447c5231a3cb27a5df9597890dd.png) filament laravel multi-panel ### Filament v4 at Scale: Multi-Panel Auth, Custom Panels, and Table Query Tuning Running Filament v4 across multiple panels with distinct auth guards and thousands of rows? This guide covers... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 13 Sep 2026 3 min read Read ](https://msaied.com/articles/filament-v4-at-scale-multi-panel-auth-custom-panels-and-table-query-tuning) [ ![Macros, Mixins, and Custom Collection Methods in Laravel](https://cdn.msaied.com/663/b8e39b17d427358aa43b5c3e8c1be908.png) laravel collections macros ### Macros, Mixins, and Custom Collection Methods in Laravel Learn how to extend Laravel's core classes with macros, mixins, and custom Collection methods — keeping your c... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 13 Sep 2026 4 min read Read ](https://msaied.com/articles/macros-mixins-and-custom-collection-methods-in-laravel-2) [ ![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)