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 leverage PostgreSQL CTEs, window functions, and LATERAL joins directly in Laravel for complex reporting and ranking queries without sacrificing maintainability.

 ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 14 Jul 2026 · Updated 14 Jul 2026 · 2 min read

ShareCopy linkCopied

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

  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
---------------------------------------------------------------

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 =