PostgreSQL CTEs & 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 CTEs, Window Functions, and Lateral Joins in Laravel        On this page       1. [  Why Raw SQL Isn't a Dirty Word ](#why-raw-sql-isnt-a-dirty-word)
2. [  Common Table Expressions (CTEs) ](#common-table-expressions-ctes)
3. [  Window Functions for Running Totals and Rankings ](#window-functions-for-running-totals-and-rankings)
4. [  LATERAL Joins for "Top N Per Group" ](#lateral-joins-for-quottop-n-per-groupquot)
5. [  Recursive CTEs for Hierarchical Data ](#recursive-ctes-for-hierarchical-data)
6. [  Keeping It Maintainable ](#keeping-it-maintainable)
7. [  Takeaways ](#takeaways)

  ![PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel](https://cdn.msaied.com/506/7d5fcddaf6c26c87a749a20b8bdffbfa.png)

  #laravel   #postgresql   #sql   #performance   #eloquent  

 PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel 
=================================================================

     4 Aug 2026      1 min read    ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg)  Mohamed Said  

       Table of contents

1. [  01   Why Raw SQL Isn't a Dirty Word  ](#why-raw-sql-isnt-a-dirty-word)
2. [  02   Common Table Expressions (CTEs)  ](#common-table-expressions-ctes)
3. [  03   Window Functions for Running Totals and Rankings  ](#window-functions-for-running-totals-and-rankings)
4. [  04   LATERAL Joins for "Top N Per Group"  ](#lateral-joins-for-quottop-n-per-groupquot)
5. [  05   Recursive CTEs for Hierarchical Data  ](#recursive-ctes-for-hierarchical-data)
6. [  06   Keeping It Maintainable  ](#keeping-it-maintainable)
7. [  07   Takeaways  ](#takeaways)

 Why Raw SQL Isn't a Dirty Word
------------------------------

Eloquent is excellent for CRUD. The moment you need ranked results per group, running totals, or hierarchical data, you are fighting the ORM instead of using the database. PostgreSQL has had CTEs, window functions, and `LATERAL` joins for years. Laravel's query builder gives you enough surface area to use them cleanly — no raw string soup required.

---

Common Table Expressions (CTEs)
-------------------------------

Laravel 9+ ships with `withExpression()` via the `DB` facade when you pull in `LaravelQueryEnumerations` — but the cleanest path is `DB::statement` for one-offs or a custom macro for reuse.

For a reporting query that needs a CTE, use `fromSub` combined with a raw `WITH` prefix:

```php
$ranked = DB::select(created_at),
            )
        );
    }
}

```

Mapping raw `stdClass` objects to typed DTOs immediately keeps the rest of your codebase type-safe.

---

LATERAL Joins for "Top N Per Group"
-----------------------------------

`LATERAL` is PostgreSQL's answer to correlated subqueries that return multiple rows. Fetching the three most recent orders per customer is a classic use case:

```php
$results = DB::select(