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://msaied.com) - [Home](https://msaied.com)
- [Projects](https://msaied.com/projects)
- [Articles](https://msaied.com/articles)
- [Certificates](https://msaied.com/certificates)
- [About](https://msaied.com#about)

           [  Contact](https://msaied.com#contact) Menu 

Menu
----

Close 

 - [HomeStart here](https://msaied.com)
- [ProjectsCase studies](https://msaied.com/projects)
- [ArticlesEngineering notes](https://msaied.com/articles)
- [CertificatesCredentials](https://msaied.com/certificates)
- [AboutHow I work](https://msaied.com#about)
- [ContactGet in touch](https://msaied.com#contact)

  [Start a conversation](https://msaied.com#contact) [WhatsApp](https://wa.me/201094619204) [Email](mailto:hello@msaied.com) 

 1. [Home](https://msaied.com)
2. /
3. [Articles](https://msaied.com/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 using raw expressions and query builder macros.

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

ShareCopy linkCopied

 ![PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel](https://cdn.msaied.com/363/8e5685c14467b502c2bcbd62b4f47f64.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 Rankings and Running Totals](#window-functions-for-rankings-and-running-totals)
4. [LATERAL Joins for Per-Row Subqueries](#lateral-joins-for-per-row-subqueries)
5. [Keeping It Testable](#keeping-it-testable)
6. [Takeaways](#takeaways)

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

Eloquent is excellent for CRUD, but complex reporting queries — running totals, ranked rows, per-group limits — fight against the ORM's grain. The right move is to drop into the query builder with targeted raw expressions while keeping everything readable and testable.

### Common Table Expressions (CTEs)

Laravel's query builder has no first-class CTE support, but `DB::statement` is the wrong tool because it discards results. Instead, use `fromRaw` or `withExpression` via the `staudenmeir/laravel-cte` package, or write it yourself with a raw `from`.

```php
// Using staudenmeir/laravel-cte (composer require staudenmeir/laravel-cte)
use Illuminate\Support\Facades\DB;

$monthlySales = DB::table('orders')
    ->selectRaw("DATE_TRUNC('month', created_at) AS month, SUM(total) AS revenue")
    ->groupByRaw("DATE_TRUNC('month', created_at)");

$results = DB::table('monthly_sales_cte')
    ->withExpression('monthly_sales_cte', $monthlySales)
    ->select('month', 'revenue')
    ->orderBy('month')
    ->get();

```

The CTE is a named subquery scoped to the outer query. PostgreSQL materialises it once, which matters when the subquery is referenced multiple times.

### Window Functions for Rankings and Running Totals

Window functions (`ROW_NUMBER`, `RANK`, `SUM ... OVER`) have no Eloquent abstraction. Use `selectRaw` and wrap the whole thing in a subquery when you need to filter on the window result.

```php
// Top-3 orders per customer by value
$ranked = DB::table('orders')
    ->selectRaw(
        'id, customer_id, total,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn'
    );

$top3 = DB::table(DB::raw("({$ranked->toSql()}) AS ranked"))
    ->mergeBindings($ranked)
    ->where('rn', 'select('users.id', 'users.name', 'o.total', 'o.created_at')
    ->join(
        DB::raw('LATERAL (SELECT total, created_at, customer_id FROM orders WHERE customer_id = users.id ORDER BY created_at DESC LIMIT 1) o'),
        DB::raw('TRUE'), '=', DB::raw('TRUE')
    )
    ->get();

```

This is verbose but explicit. Extract it into a query scope or a dedicated repository method to keep call sites clean.

### Keeping It Testable

Wrap complex queries in dedicated query classes or repository methods. Inject `\Illuminate\Database\ConnectionInterface` rather than calling `DB::` statically so you can swap in an in-memory SQLite connection for unit tests — though for window functions and LATERAL you will need a real PostgreSQL instance, so lean on feature tests with a dedicated test database.

```php
class TopOrdersPerCustomerQuery
{
    public function __construct(
        private readonly \Illuminate\Database\ConnectionInterface $db
    ) {}

    public function get(int $limit = 3): \Illuminate\Support\Collection
    {
        $ranked = $this->db->table('orders')
            ->selectRaw('id, customer_id, total, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn');

        return $this->db->table(DB::raw("({$ranked->toSql()}) AS ranked"))
            ->mergeBindings($ranked)
            ->where('rn', '