PostgreSQL CTEs & Window Functions 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. [  Why Reach Beyond Eloquent's Comfort Zone ](#why-reach-beyond-eloquents-comfort-zone)
2. [  Common Table Expressions (CTEs) ](#common-table-expressions-ctes)
3. [  Window Functions for Rankings and Running Totals ](#window-functions-for-rankings-and-running-totals)
4. [  Tip: Avoid Hydrating Eloquent Models Here ](#tip-avoid-hydrating-eloquent-models-here)
5. [  LATERAL Joins: Correlated Subqueries Done Right ](#lateral-joins-correlated-subqueries-done-right)
6. [  Keeping It Testable ](#keeping-it-testable)
7. [  Takeaways ](#takeaways)

  ![PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel](https://cdn.msaied.com/175/8e5685c14467b502c2bcbd62b4f47f64.png)

  #laravel   #postgresql   #eloquent   #performance  

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

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

       Table of contents

1. [  01   Why Reach Beyond Eloquent's Comfort Zone  ](#why-reach-beyond-eloquents-comfort-zone)
2. [  02   Common Table Expressions (CTEs)  ](#common-table-expressions-ctes)
3. [  03   Window Functions for Rankings and Running Totals  ](#window-functions-for-rankings-and-running-totals)
4. [  04   Tip: Avoid Hydrating Eloquent Models Here  ](#tip-avoid-hydrating-eloquent-models-here)
5. [  05   LATERAL Joins: Correlated Subqueries Done Right  ](#lateral-joins-correlated-subqueries-done-right)
6. [  06   Keeping It Testable  ](#keeping-it-testable)
7. [  07   Takeaways  ](#takeaways)

 Why Reach Beyond Eloquent's Comfort Zone
----------------------------------------

Eloquent handles 90% of your day-to-day queries elegantly. But when you're building reporting dashboards, ranking rows, or computing running totals, you hit a wall. PostgreSQL has solved these problems for decades with CTEs, window functions, and `LATERAL` joins. Laravel's query builder doesn't abstract them away — it lets you compose them with raw expressions, keeping SQL readable and your PHP clean.

---

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

A CTE names a subquery so you can reference it multiple times or chain logic clearly. Laravel has no first-class `withCte()` method, but `DB::statement` and `fromRaw` give you full control.

```php
$results = DB::table(
    DB::raw('(
        WITH ranked_orders AS (
            SELECT
                customer_id,
                total,
                ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
            FROM orders
        )
        SELECT customer_id, total
        FROM ranked_orders
        WHERE rn = 1
    ) AS latest_orders')
)->get();

```

For reuse across the codebase, wrap this in a dedicated query class:

```php
final class LatestOrderPerCustomerQuery
{
    public function get(): Collection
    {
        return DB::table(DB::raw('(
            WITH ranked AS (
                SELECT customer_id, total,
                    ROW_NUMBER() OVER (
                        PARTITION BY customer_id ORDER BY created_at DESC
                    ) AS rn
                FROM orders
            )
            SELECT customer_id, total FROM ranked WHERE rn = 1
        ) AS q'))->get();
    }
}

```

This keeps your controllers thin and the SQL co-located with the intent.

---

Window Functions for Rankings and Running Totals
------------------------------------------------

Window functions compute values across a set of rows related to the current row — without collapsing them like `GROUP BY` does.

```php
$rows = DB::select(id,
    customerId: $row->customer_id,
    total: $row->total,
    runningTotal: $row->running_total,
    rank: $row->rank,
));

```

### Tip: Avoid Hydrating Eloquent Models Here

Using `DB::select()` instead of `Model::all()` skips Eloquent hydration entirely — a meaningful win when you're pulling thousands of rows for a report.

---

LATERAL Joins: Correlated Subqueries Done Right
-----------------------------------------------

A `LATERAL` join lets the right-hand subquery reference columns from the left-hand table — something a plain subquery cannot do. It's ideal for "top N per group" patterns.

```php
$results = DB::table('customers')
    ->joinSub(
        DB::raw('(
            SELECT o.customer_id, o.total, o.created_at
            FROM orders o
            WHERE o.customer_id = customers.id
            ORDER BY o.created_at DESC
            LIMIT 3
        )'),
        'recent',
        'recent.customer_id',
        '=',
        'customers.id'
    )
    ->select('customers.name', 'recent.total', 'recent.created_at')
    ->get();

```

Because `joinSub` wraps in parentheses automatically, you need the `LATERAL` keyword explicitly:

```php
$results = DB::table('customers')
    ->join(
        DB::raw('LATERAL (
            SELECT total, created_at
            FROM orders
            WHERE orders.customer_id = customers.id
            ORDER BY created_at DESC
            LIMIT 3
        ) recent'),
        DB::raw('true'),
        '=',
        DB::raw('true')
    )
    ->select('customers.name', 'recent.total', 'recent.created_at')
    ->get();

```

The `ON true` trick satisfies Laravel's join signature while letting PostgreSQL handle the correlation.

---

Keeping It Testable
-------------------

Wrap each complex query in a dedicated class and test it against a real PostgreSQL database (not SQLite) using Pest:

```php
it('returns the latest order per customer', function () {
    Customer::factory()->has(Order::factory()->count(3))->create();

    $results = (new LatestOrderPerCustomerQuery)->get();

    expect($results)->toHaveCount(1);
});

```

SQLite doesn't support `LATERAL` or many window functions — always run these tests against Postgres in CI.

---

Takeaways
---------

- **CTEs** name subqueries for readability and reuse; wrap them in dedicated query classes.
- **Window functions** rank and aggregate without collapsing rows — far cleaner than multiple queries.
- **LATERAL joins** enable correlated subqueries that reference the outer table, perfect for top-N-per-group.
- Use `DB::select()` or `DB::table()` with raw expressions; skip Eloquent hydration for pure reporting.
- Always test PostgreSQL-specific queries against a real Postgres instance in CI — SQLite will silently miss incompatibilities.

 Found this useful?

          [  ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fpostgresql-ctes-window-functions-and-lateral-joins-in-laravel&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) 

 Frequently Asked Questions 
----------------------------

  3 questions  

     Q01  Can I use CTEs with Eloquent models instead of DB::table?        Yes. You can call `YourModel::from(DB::raw('(WITH ... ) AS alias'))` to hydrate Eloquent models from a CTE result, but for pure reporting queries the overhead of hydration is rarely worth it. 

      Q02  Will LATERAL joins work with MySQL in the same Laravel codebase?        MySQL 8.0+ supports LATERAL joins, but the syntax and optimizer behaviour differ from PostgreSQL. If you target both databases, abstract the query behind an interface and provide separate implementations per driver. 

      Q03  How do I prevent SQL injection when building raw expressions?        Always use query builder bindings via `DB::raw()` combined with `->addBinding()`, or pass user input through `DB::table()->where()` clauses rather than string-interpolating it into raw SQL fragments. 

  Continue reading

 More Articles 
---------------

 [ View all    ](https://msaied.com/articles) 

 [ ![Recursive Queries in MySQL 8 with Laravel: CTEs, Hierarchies, and Adjacency Lists](https://cdn.msaied.com/176/e761e34c4eeac562198091ba035908ca.png) laravel mysql database 

### Recursive Queries in MySQL 8 with Laravel: CTEs, Hierarchies, and Adjacency Lists

MySQL 8 supports recursive CTEs. Learn how to query adjacency-list hierarchies in Laravel without pulling the...

  ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg)  Mohamed Said 

 14 Jun 2026     2 min read  

  Read    

 ](https://msaied.com/articles/recursive-queries-in-mysql-8-with-laravel-ctes-hierarchies-and-adjacency-lists) [ ![Eloquent Custom Casts: Encapsulating Value Objects Without the Bloat](https://cdn.msaied.com/174/2c75896ee4182bb2f66e2c93bed18796.png) laravel eloquent ddd 

### Eloquent Custom Casts: Encapsulating Value Objects Without the Bloat

Custom Eloquent casts let you bind rich value objects directly to model attributes. This article shows how to...

  ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg)  Mohamed Said 

 14 Jun 2026     1 min read  

  Read    

 ](https://msaied.com/articles/eloquent-custom-casts-encapsulating-value-objects-without-the-bloat) [ ![Filament v4 Schema-Based Forms: Unified Schema API in Practice](https://cdn.msaied.com/173/6e0d9faa9137cb296e37831c3645e7ba.png) filament laravel filament-v4 

### Filament v4 Schema-Based Forms: Unified Schema API in Practice

Filament v4 replaces scattered form/infolist definitions with a single Schema API. This post walks through rea...

  ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg)  Mohamed Said 

 14 Jun 2026     3 min read  

  Read    

 ](https://msaied.com/articles/filament-v4-schema-based-forms-unified-schema-api-in-practice) 

   [  ![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)