PostgreSQL CTEs, Window Functions & 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, Window Functions, and Lateral Joins in Laravel On this page 1. [ Beyond Eloquent Basics: Advanced PostgreSQL in Laravel ](#beyond-eloquent-basics-advanced-postgresql-in-laravel) 2. [ Common Table Expressions (CTEs) ](#common-table-expressions-ctes) 3. [ Window Functions ](#window-functions) 4. [ Running totals and rankings ](#running-totals-and-rankings) 5. [ Lag/lead for time-series deltas ](#laglead-for-time-series-deltas) 6. [ LATERAL Joins ](#lateral-joins) 7. [ Fetch the latest N rows per group ](#fetch-the-latest-n-rows-per-group) 8. [ Combining All Three ](#combining-all-three) 9. [ Key Takeaways ](#key-takeaways) ![PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel](https://cdn.msaied.com/303/bbdbe4ee10b30fa9dfecef00698a1c9f.png) #laravel #postgresql #query-builder #performance PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel ================================================================= 27 Jun 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 9 sections 1. [ 01 Beyond Eloquent Basics: Advanced PostgreSQL in Laravel ](#beyond-eloquent-basics-advanced-postgresql-in-laravel) 2. [ 02 Common Table Expressions (CTEs) ](#common-table-expressions-ctes) 3. [ 03 Window Functions ](#window-functions) 4. [ 04 Running totals and rankings ](#running-totals-and-rankings) 5. [ 05 Lag/lead for time-series deltas ](#laglead-for-time-series-deltas) 6. [ 06 LATERAL Joins ](#lateral-joins) 7. [ 07 Fetch the latest N rows per group ](#fetch-the-latest-n-rows-per-group) 8. [ 08 Combining All Three ](#combining-all-three) 9. [ 09 Key Takeaways ](#key-takeaways) Beyond Eloquent Basics: Advanced PostgreSQL in Laravel ------------------------------------------------------ Eloquent is excellent for CRUD, but analytical queries, rankings, and hierarchical data quickly push it to its limits. PostgreSQL's CTEs, window functions, and `LATERAL` joins are purpose-built for these cases. Laravel's query builder lets you drop into raw SQL fragments without abandoning the fluent interface entirely. --- Common Table Expressions (CTEs) ------------------------------- A CTE names a subquery so you can reference it multiple times or build readable multi-step logic. ```php $results = DB::query() ->withExpression('ranked_orders', function ($query) { $query->from('orders') ->select('customer_id', 'total', 'created_at') ->where('status', 'completed'); }) ->from('ranked_orders') ->where('total', '>', 500) ->get(); ``` > `withExpression` is provided by the [staudenmeir/laravel-cte](https://github.com/staudenmeir/laravel-cte) package, which adds first-class CTE support to Laravel's query builder. For **recursive CTEs** — think category trees or org charts — the same package exposes `withRecursiveExpression`: ```php $tree = DB::query() ->withRecursiveExpression('category_tree', function ($query) { // Anchor: root categories $query->from('categories') ->whereNull('parent_id') ->select('id', 'name', 'parent_id', DB::raw('0 as depth')) ->unionAll( // Recursive member DB::table('categories as c') ->join('category_tree as ct', 'c.parent_id', '=', 'ct.id') ->select('c.id', 'c.name', 'c.parent_id', DB::raw('ct.depth + 1')) ); }) ->from('category_tree') ->orderBy('depth') ->get(); ``` This replaces multiple round-trips or application-side tree assembly with a single query. --- Window Functions ---------------- Window functions compute values across a set of rows related to the current row — without collapsing them into groups. ### Running totals and rankings ```php $rows = DB::table('orders') ->select( 'customer_id', 'total', 'created_at', DB::raw('SUM(total) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total'), DB::raw('RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rank_by_value') ) ->where('status', 'completed') ->get(); ``` You get per-customer running totals and value rankings in one pass. The equivalent in PHP would require loading all rows, grouping them, and iterating — far more memory and time. ### Lag/lead for time-series deltas ```php DB::table('daily_metrics') ->select( 'date', 'revenue', DB::raw("LAG(revenue) OVER (ORDER BY date) AS prev_revenue"), DB::raw("revenue - LAG(revenue) OVER (ORDER BY date) AS delta") ) ->orderBy('date') ->get(); ``` This is the idiomatic way to compute day-over-day changes without a self-join. --- LATERAL Joins ------------- A `LATERAL` join lets each row of the left table be referenced inside the right subquery — effectively a correlated subquery that returns a set of rows rather than a scalar. ### Fetch the latest N rows per group ```php $customers = DB::table('customers as c') ->joinLateral( DB::table('orders') ->whereColumn('orders.customer_id', 'c.id') ->orderByDesc('created_at') ->limit(3) ->select('id as order_id', 'total', 'created_at'), 'recent_orders' ) ->select('c.id', 'c.name', 'recent_orders.*') ->get(); ``` `joinLateral` was added to Laravel's query builder in Laravel 9.x. It compiles to `JOIN LATERAL (...) ON TRUE`, which PostgreSQL handles efficiently with an index on `(customer_id, created_at DESC)`. Compare this to the classic `ROW_NUMBER()` window-function approach — both work, but `LATERAL` is often more readable when the subquery is complex. --- Combining All Three ------------------- Real analytical dashboards often chain all three techniques: ```php DB::query() ->withExpression('active_customers', fn($q) => $q->from('customers')->where('active', true) ) ->from('active_customers as ac') ->joinLateral( DB::table('orders') ->whereColumn('orders.customer_id', 'ac.id') ->select( 'customer_id', DB::raw('SUM(total) AS lifetime_value'), DB::raw('RANK() OVER (ORDER BY SUM(total) DESC) AS value_rank') ) ->groupBy('customer_id'), 'stats' ) ->select('ac.id', 'ac.name', 'stats.lifetime_value', 'stats.value_rank') ->orderBy('stats.value_rank') ->get(); ``` --- Key Takeaways ------------- - Use **CTEs** to name and reuse subqueries; use recursive CTEs for tree/graph traversal. - **Window functions** compute rankings, running totals, and deltas in a single pass — no PHP-side aggregation needed. - **`LATERAL` joins** replace "top N per group" patterns cleanly and are natively supported by `joinLateral()` in Laravel 9+. - Keep raw SQL fragments inside `DB::raw()` or dedicated query builder methods; avoid embedding them in Eloquent model methods where they become invisible to static analysis. - Always verify query plans with `EXPLAIN (ANALYZE, BUFFERS)` — CTEs in PostgreSQL 12+ are not always optimization fences, but complex ones can still surprise you. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-ctes-window-functions-and-lateral-joins-in-laravel-1&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-1) Frequently Asked Questions ---------------------------- 3 questions Q01 Does Laravel's query builder support CTEs natively? Not out of the box. The staudenmeir/laravel-cte package adds `withExpression` and `withRecursiveExpression` to the query builder. For lateral joins, `joinLateral()` is built into Laravel 9+ without any extra package. Q02 Are window functions safe to use inside Eloquent models? Yes, but keep them in dedicated query scopes or repository methods using `DB::raw()`. Avoid embedding them in global scopes, as they can interfere with aggregate queries Eloquent runs internally (e.g., for pagination counts). Q03 When should I prefer a LATERAL join over a ROW\_NUMBER() window function for top-N-per-group? LATERAL is cleaner when the subquery has its own ORDER BY and LIMIT, and when you want to avoid a wrapping SELECT to filter on the rank. ROW_NUMBER() is preferable when you need the rank value itself in the result set for further filtering or display. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Livewire v3 Performance: Optimistic UI, Wire:model.live Debouncing, and Dirty State](https://cdn.msaied.com/305/98e4a28fdff5a8448e19534c07bb391d.png) livewire laravel performance ### Livewire v3 Performance: Optimistic UI, Wire:model.live Debouncing, and Dirty State Practical techniques for squeezing real performance out of Livewire v3: controlling when the server round-trip... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 27 Jun 2026 3 min read Read ](https://msaied.com/articles/livewire-v3-performance-optimistic-ui-wiremodellive-debouncing-and-dirty-state) [ ![Testing Filament Resources, Actions, and Form Assertions with Pest](https://cdn.msaied.com/304/2d16aec8179bbaae9647506470a85e40.png) filament pest testing ### Testing Filament Resources, Actions, and Form Assertions with Pest A practical guide to writing reliable Pest tests for Filament v3/v4 resources, covering table actions, form su... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 27 Jun 2026 3 min read Read ](https://msaied.com/articles/testing-filament-resources-actions-and-form-assertions-with-pest-1) [ ![Eloquent N+1 at Scale: Eager Loading Strategies, Subquery Selects, and Lazy Eager Loading](https://cdn.msaied.com/302/40500f25f8a29e6cd6eac4938f7211d0.png) laravel eloquent performance ### Eloquent N+1 at Scale: Eager Loading Strategies, Subquery Selects, and Lazy Eager Loading N+1 queries silently kill throughput in production. This guide goes beyond basic with() calls to cover subquer... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 27 Jun 2026 4 min read Read ](https://msaied.com/articles/eloquent-n1-at-scale-eager-loading-strategies-subquery-selects-and-lazy-eager-loading) [ ![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)