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. [  PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel ](#postgresql-ctes-window-functions-and-lateral-joins-in-laravel)
2. [  Common Table Expressions (CTEs) ](#common-table-expressions-ctes)
3. [  Window Functions for Ranking and Running Totals ](#window-functions-for-ranking-and-running-totals)
4. [  LATERAL Joins: The N+1 Killer for Latest-Per-Group ](#lateral-joins-the-n1-killer-for-latest-per-group)
5. [  Encapsulating Raw SQL Safely ](#encapsulating-raw-sql-safely)
6. [  Takeaways ](#takeaways)

  ![PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel](https://cdn.msaied.com/425/07a543ad383be3d55de9ed21cf7509d5.png)

  #laravel   #postgresql   #eloquent   #database   #performance  

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

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

       Table of contents

1. [  01   PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel  ](#postgresql-ctes-window-functions-and-lateral-joins-in-laravel)
2. [  02   Common Table Expressions (CTEs)  ](#common-table-expressions-ctes)
3. [  03   Window Functions for Ranking and Running Totals  ](#window-functions-for-ranking-and-running-totals)
4. [  04   LATERAL Joins: The N+1 Killer for Latest-Per-Group  ](#lateral-joins-the-n1-killer-for-latest-per-group)
5. [  05   Encapsulating Raw SQL Safely  ](#encapsulating-raw-sql-safely)
6. [  06   Takeaways  ](#takeaways)

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

Eloquent is excellent for CRUD. But the moment you need ranked results per group, running totals, or the latest N rows per foreign key, you're fighting the ORM instead of using the database. PostgreSQL has had the answers for years — CTEs, window functions, and `LATERAL` joins. Laravel's query builder exposes enough surface area to use all three cleanly.

### Common Table Expressions (CTEs)

Laravel 9+ ships with `withExpression()` via the `DB` facade when you pull in `LaravelFreelancerNl\LaravelEnum` — wait, that's wrong. The method is available natively through `DB::query()->withExpression()` only in some community packages. The idiomatic Laravel approach is `DB::statement` or `DB::select` with a raw CTE prefix, or using a subquery macro.

Here's the cleanest pattern without a package:

```php
$cte = DB::table('orders')
    ->select('user_id', DB::raw('SUM(total) as lifetime_value'))
    ->groupBy('user_id');

$results = DB::table(
        DB::raw('('. $cte->toSql() .') as order_totals')
    )
    ->mergeBindings($cte)
    ->join('users', 'users.id', '=', 'order_totals.user_id')
    ->select('users.email', 'order_totals.lifetime_value')
    ->orderByDesc('lifetime_value')
    ->get();

```

For a true `WITH` clause, drop to `DB::select` with a named binding:

```php
$sql = addSelect(DB::raw(
        'ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY created_at DESC) AS rn'
    ))
    ->where('account_id', $accountId)
    ->get();

```

Wrap this in a query object or a dedicated repository method so the raw SQL never leaks into controllers.

### LATERAL Joins: The N+1 Killer for Latest-Per-Group

Fetching the most recent order per user is a classic N+1 trap. A `LATERAL` join solves it in one query:

```php
$users = DB::table('users')
    ->select('users.id', 'users.email', 'latest.total', 'latest.created_at')
    ->joinSub(
        DB::table('orders')
            ->select('total', 'created_at', 'user_id')
            ->whereColumn('orders.user_id', 'users.id')
            ->orderByDesc('created_at')
            ->limit(1),
        'latest',
        DB::raw('true'), // LATERAL — no ON condition needed
        'cross'
    )
    ->get();

```

Unfortunately `joinSub` doesn't emit `LATERAL` by default. Use `DB::raw` for the join clause directly:

```php
$sql =