PostgreSQL Window Functions in Laravel | Mohamed Said [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://www.msaied.com) [ Home ](https://www.msaied.com) [ Projects ](https://www.msaied.com/projects) [ Articles ](https://www.msaied.com/articles) [ Certificates ](https://www.msaied.com/certificates) [ Contact ](https://www.msaied.com#contact-section) [ ](https://github.com/EG-Mohamed) [ Home ](https://www.msaied.com) [ Projects ](https://www.msaied.com/projects) [ Articles ](https://www.msaied.com/articles) [ Certificates ](https://www.msaied.com/certificates) [ Contact ](https://www.msaied.com#contact-section) [ home ](https://www.msaied.com) [ articles ](https://www.msaied.com/articles) PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection On this page 1. [ Why Window Functions Belong in Your Laravel Toolkit ](#why-window-functions-belong-in-your-laravel-toolkit) 2. [ ROW\_NUMBER for Per-Partition Ranking ](#row-number-for-per-partition-ranking) 3. [ Running Totals with SUM OVER ](#running-totals-with-sum-over) 4. [ Gap Detection with LAG ](#gap-detection-with-lag) 5. [ Wrapping Results in Eloquent Models ](#wrapping-results-in-eloquent-models) 6. [ Query Plan Sanity Check ](#query-plan-sanity-check) 7. [ Key Takeaways ](#key-takeaways) ![PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection](https://cdn.msaied.com/546/f045f6411aa801b18d8a06d0518d540a.png) #laravel #postgresql #sql #performance PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection ==================================================================================== 14 Aug 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 Why Window Functions Belong in Your Laravel Toolkit ](#why-window-functions-belong-in-your-laravel-toolkit) 2. [ 02 ROW\_NUMBER for Per-Partition Ranking ](#row-number-for-per-partition-ranking) 3. [ 03 Running Totals with SUM OVER ](#running-totals-with-sum-over) 4. [ 04 Gap Detection with LAG ](#gap-detection-with-lag) 5. [ 05 Wrapping Results in Eloquent Models ](#wrapping-results-in-eloquent-models) 6. [ 06 Query Plan Sanity Check ](#query-plan-sanity-check) 7. [ 07 Key Takeaways ](#key-takeaways) Why Window Functions Belong in Your Laravel Toolkit --------------------------------------------------- Window functions execute across a *set* of rows related to the current row without collapsing them into a single output row the way `GROUP BY` does. That distinction matters: you keep every row while still computing aggregates, ranks, or offsets across a logical partition. Doing the same work in PHP means loading thousands of rows into memory and iterating — a trade-off you should rarely accept. PostgreSQL has supported window functions since version 8.4. Laravel's query builder does not have a dedicated API for them, but `selectRaw`, `DB::raw`, and subquery wrapping give you everything you need. --- ROW\_NUMBER for Per-Partition Ranking ------------------------------------- Imagine a `orders` table and you want the most recent order per customer without a correlated subquery. ```php $ranked = DB::table('orders') ->selectRaw( 'id, customer_id, total, created_at, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY created_at DESC ) AS rn' ); $latest = DB::query() ->fromSub($ranked, 'ranked') ->where('rn', 1) ->get(); ``` The inner query assigns a rank; the outer query filters to rank 1. PostgreSQL executes this as a single pass with a window sort — far cheaper than a `MAX` self-join on large tables. --- Running Totals with SUM OVER ---------------------------- A running total is the canonical window function example, but it comes up constantly in financial dashboards and audit trails. ```php $ledger = DB::table('transactions') ->where('account_id', $accountId) ->selectRaw( 'id, amount, created_at, SUM(amount) OVER ( PARTITION BY account_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_balance' ) ->orderBy('created_at') ->get(); ``` The `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` frame clause is explicit about what "running" means. Omitting it relies on the default frame, which changes when you add `ORDER BY` — being explicit prevents subtle bugs. --- Gap Detection with LAG ---------------------- `LAG` and `LEAD` access the previous or next row's value without a self-join. This is useful for detecting gaps in sequential data — invoice numbers, ticket IDs, or scheduled slots. ```php $gaps = DB::query()->fromSub( DB::table('invoices') ->selectRaw( 'invoice_number, LAG(invoice_number) OVER (ORDER BY invoice_number) AS prev_number' ), 'lagged' ) ->whereRaw('invoice_number prev_number + 1') ->whereNotNull('prev_number') ->get(); ``` Each row in `lagged` carries the previous invoice number. The outer filter surfaces any row where the sequence is broken. A PHP loop doing the same work would require the entire result set in memory first. --- Wrapping Results in Eloquent Models ----------------------------------- You can hydrate Eloquent models from raw window-function queries using `hydrate`: ```php $rows = DB::select( 'SELECT *, RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard_entries WHERE season_id = ?', [$seasonId] ); $entries = LeaderboardEntry::hydrate($rows); // $entries[0]->rank is accessible as a dynamic attribute ``` The extra columns (`rank` here) become accessible as dynamic properties. They will not be persisted if you call `save()`, but they are perfect for read-heavy display logic. --- Query Plan Sanity Check ----------------------- Always verify the plan when adding window functions to hot paths: ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT id, SUM(amount) OVER (PARTITION BY account_id ORDER BY created_at) FROM transactions WHERE account_id = 42; ``` Look for `WindowAgg` in the plan. If you see a sequential scan on a large table, a partial index on `(account_id, created_at)` will typically convert it to an `Index Scan` and eliminate the sort step entirely. --- Key Takeaways ------------- - Use `selectRaw` or `DB::raw` to embed window functions; no special query builder API is needed. - Always specify the frame clause (`ROWS BETWEEN ...`) to avoid frame-default surprises. - Wrap window queries in a subquery (`fromSub`) when you need to filter on the computed column. - `LAG`/`LEAD` replace self-joins for sequential comparisons — cleaner SQL, better plans. - Hydrate Eloquent models from raw results to keep presentation logic in the model layer. - Verify execution plans and add partial covering indexes on partition + order columns for hot queries. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fwww.msaied.com%2Farticles%2Fpostgresql-window-functions-in-laravel-ranking-running-totals-and-gap-detection-1&text=PostgreSQL+Window+Functions+in+Laravel%3A+Ranking%2C+Running+Totals%2C+and+Gap+Detection) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fwww.msaied.com%2Farticles%2Fpostgresql-window-functions-in-laravel-ranking-running-totals-and-gap-detection-1) Frequently Asked Questions ---------------------------- 3 questions Q01 Can I use window functions with Eloquent scopes? Not directly inside a scope, but you can wrap an Eloquent query as a subquery using `DB::query()->fromSub(YourModel::query(), 'sub')->selectRaw(...)` and still benefit from scopes applied to the inner builder. Q02 Do window functions work with Laravel's pagination? Standard `paginate()` wraps your query in a COUNT subquery, which can conflict with window function aliases. Use `simplePaginate` or manual LIMIT/OFFSET on a subquery that already contains the window computation. Q03 Will these queries work on MySQL too? MySQL 8.0+ supports most window functions with the same syntax. However, frame clause support and optimizer behaviour differ. If you target both engines, test EXPLAIN output on each separately. Continue reading More Articles --------------- [ View all ](https://www.msaied.com/articles) [ ![Octane Worker Lifecycle, State Leakage, and Memory Management in Production](https://cdn.msaied.com/554/8cc265358b47e59601a66d1e247eba9a.png) laravel octane performance ### Octane Worker Lifecycle, State Leakage, and Memory Management in Production Laravel Octane keeps workers alive across requests, which means static state, resolved singletons, and stale d... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 16 Aug 2026 3 min read Read ](https://www.msaied.com/articles/octane-worker-lifecycle-state-leakage-and-memory-management-in-production-2) [ ![Job Batching with Laravel Horizon: Reliable Async Workflows at Scale](https://cdn.msaied.com/553/b794b736bfd84f3cbcc6218319916544.png) laravel queues horizon ### Job Batching with Laravel Horizon: Reliable Async Workflows at Scale Learn how to combine Laravel's job batching API with Horizon's queue supervision to build fault-tolerant async... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 15 Aug 2026 3 min read Read ](https://www.msaied.com/articles/job-batching-with-laravel-horizon-reliable-async-workflows-at-scale) [ ![Contextual Eloquent Casts: Custom Cast Classes, Value Objects, and Inbound-Only Transforms](https://cdn.msaied.com/552/a7825c0c6f53d934f84fce522573eafb.png) laravel eloquent value-objects ### Contextual Eloquent Casts: Custom Cast Classes, Value Objects, and Inbound-Only Transforms Go beyond primitive casts. Learn how to build custom Eloquent cast classes that hydrate value objects, handle... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 15 Aug 2026 1 min read Read ](https://www.msaied.com/articles/contextual-eloquent-casts-custom-cast-classes-value-objects-and-inbound-only-transforms) [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://www.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://www.msaied.com) - [Projects](https://www.msaied.com/projects) - [Articles](https://www.msaied.com/articles) - [Certificates](https://www.msaied.com/certificates) - [Contact](https://www.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)