PostgreSQL CTEs, Window Functions & Lateral Joins 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 CTEs, Window Functions, and Lateral Joins in Laravel PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel ================================================================ Go beyond basic Eloquent queries. Learn how to harness PostgreSQL CTEs, window functions, and LATERAL joins directly from Laravel to solve real analytical and hierarchical data problems. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 27 Jun 2026 · Updated 27 Jun 2026 · 4 min read ShareCopy linkCopied ![PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel](https://cdn.msaied.com/303/bbdbe4ee10b30fa9dfecef00698a1c9f.png) 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) 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. - [laravel](https://www.msaied.com/public/articles?search=laravel) - [postgresql](https://www.msaied.com/public/articles?search=postgresql) - [query-builder](https://www.msaied.com/public/articles?search=query-builder) - [performance](https://www.msaied.com/public/articles?search=performance) Frequently asked questions --------------------------- 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. 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). 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. ![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 articleEloquent N+1 at Scale: Eager Loading Strategies, Subquery Selects, and Lazy Eager Loading](https://www.msaied.com/public/articles/eloquent-n1-at-scale-eager-loading-strategies-subquery-selects-and-lazy-eager-loading) [Next articleTesting Filament Resources, Actions, and Form Assertions with Pest](https://www.msaied.com/public/articles/testing-filament-resources-actions-and-form-assertions-with-pest-1) 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) ### 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/743/8998fac3a41451ab3fe1588194e17a43.png) Filament · 3 min read### Securing Filament Plugins with Plumb: Automated Security Scoring for PHP Packages 5 Oct 2026 ](https://www.msaied.com/public/articles/securing-filament-plugins-with-plumb-automated-security-scoring-for-php-packages) [ ![](https://cdn.msaied.com/742/2d02018669cdeedccb5de2efb898f0ee.png) Filament · 3 min read### Filament v3.3.56 Released: File Hash Names and Livewire Upload Fix 5 Oct 2026 ](https://www.msaied.com/public/articles/filament-v3356-released-file-hash-names-and-livewire-upload-fix) [ ![](https://cdn.msaied.com/741/5b55c123ad08e4d34e1f4b99ad6a428b.png) · 3 min read### Filament v4 Schema-Based Forms, Infolists, and the Unified Schema API 5 Oct 2026 ](https://www.msaied.com/public/articles/filament-v4-schema-based-forms-infolists-and-the-unified-schema-api-5) 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)