PostgreSQL Window Functions in Laravel | Mohamed Said [Skip to content](#main) [ ![](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed SaidLaravel Backend Engineer ](https://www.msaied.com/public) - [Home](https://www.msaied.com/public) - [Projects](https://www.msaied.com/public/projects) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [About](https://www.msaied.com/public#about) [ Contact](https://www.msaied.com/public#contact) Menu Menu ---- Close - [HomeStart here](https://www.msaied.com/public) - [ProjectsCase studies](https://www.msaied.com/public/projects) - [ArticlesEngineering notes](https://www.msaied.com/public/articles) - [CertificatesCredentials](https://www.msaied.com/public/certificates) - [AboutHow I work](https://www.msaied.com/public#about) - [ContactGet in touch](https://www.msaied.com/public#contact) [Start a conversation](https://www.msaied.com/public#contact) [WhatsApp](https://wa.me/201094619204) [Email](mailto:hello@msaied.com) 1. [Home](https://www.msaied.com/public) 2. / 3. [Articles](https://www.msaied.com/public/articles) 4. / 5. PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection =================================================================================== Window functions let you compute rankings, running totals, and gaps directly in SQL without self-joins or PHP loops. Here is how to use them cleanly from Laravel's query builder. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 14 Aug 2026 · Updated 14 Aug 2026 · 4 min read ShareCopy linkCopied ![PostgreSQL Window Functions in Laravel: Ranking, Running Totals, and Gap Detection](https://cdn.msaied.com/546/f045f6411aa801b18d8a06d0518d540a.png) 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) 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. - [laravel](https://www.msaied.com/public/articles?search=laravel) - [postgresql](https://www.msaied.com/public/articles?search=postgresql) - [sql](https://www.msaied.com/public/articles?search=sql) - [performance](https://www.msaied.com/public/articles?search=performance) Frequently asked questions --------------------------- 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. 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. 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. ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp)About the author ---------------- [Mohamed Said](https://www.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://www.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 articleEvent Sourcing in Laravel: Aggregates, Projectors, and Reactors Without the Ceremony](https://www.msaied.com/public/articles/event-sourcing-in-laravel-aggregates-projectors-and-reactors-without-the-ceremony) [Next articleFilament v3 Custom Field Plugins: Building Reusable Inputs with Full Form Integration](https://www.msaied.com/public/articles/filament-v3-custom-field-plugins-building-reusable-inputs-with-full-form-integration) 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) ### Have a technical challenge? Tell me what you’re building. I reply within two working days. [Start a conversation](https://www.msaied.com/public#contact) Related articles ----------------- [ ![](https://cdn.msaied.com/740/cce86edc21eddcbdd2f2454fadaf9c70.png) · 3 min read### The Pipeline Pattern in Laravel: Custom Pipelines Beyond Middleware 5 Oct 2026 ](https://www.msaied.com/public/articles/the-pipeline-pattern-in-laravel-custom-pipelines-beyond-middleware-1) [ ![](https://cdn.msaied.com/739/2d6897fdcdcf090613f96f72a64b8a78.png) · 4 min read### MySQL Full-Text Search in Laravel: Indexes, Relevance Scoring, and Boolean Mode 4 Oct 2026 ](https://www.msaied.com/public/articles/mysql-full-text-search-in-laravel-indexes-relevance-scoring-and-boolean-mode) [ ![](https://cdn.msaied.com/738/073696a3fefe18bec825beec5ac658f5.png) · 4 min read### Laravel Queue Rate-Limited Middleware: Throttling Jobs Without Losing Work 4 Oct 2026 ](https://www.msaied.com/public/articles/laravel-queue-rate-limited-middleware-throttling-jobs-without-losing-work) Have a technical challenge? ---------------------------- Tell me what you’re building. I reply within two working days. [Discuss your project ↗](https://www.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://www.msaied.com/public) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.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://www.msaied.com/public/sitemap.xml)