PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel

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.

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

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.

$rows = DB::select(<<<SQL
    SELECT
        id,
        customer_id,
        total,
        SUM(total) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total,
        RANK()      OVER (PARTITION BY customer_id ORDER BY total DESC) AS rank
    FROM orders
    ORDER BY customer_id, created_at
SQL);

Map the raw results onto a lightweight DTO to keep your domain layer clean:

$orders = collect($rows)->map(fn(object $row) => new OrderSummaryDto(
    id: $row->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.

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

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

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.