PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel

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.

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

// 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', '<=', 3)
    ->get();

The double-query pattern (inner window, outer filter) is idiomatic PostgreSQL. Trying to filter on a window alias in the same SELECT is a syntax error — the subquery wrapper is not optional.

Running totals follow the same pattern:

DB::table('transactions')
    ->selectRaw(
        'id, amount, created_at,
         SUM(amount) OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total'
    )
    ->orderBy('created_at')
    ->get();

LATERAL Joins for Per-Row Subqueries

A LATERAL join lets the right-hand subquery reference columns from the left-hand table — think of it as a correlated subquery that can return multiple rows.

Use case: fetch each user alongside their single most recent order without a window function.

$users = DB::table('users')
    ->select('users.id', 'users.name', 'latest.total', 'latest.created_at')
    ->joinSub(
        DB::table('orders')
            ->selectRaw('customer_id, total, created_at')
            ->orderByDesc('created_at')
            ->limit(1),
        'latest',
        // LATERAL requires a raw ON clause; joinSub adds INNER JOIN by default
        DB::raw('latest.customer_id = users.id'),
        null,
        'cross' // workaround: use crossJoinSub + whereColumn
    );

Because Laravel's joinSub does not emit LATERAL, the cleanest approach is a raw join:

$users = DB::table('users')
    ->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.

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', '<=', $limit)
            ->get();
    }
}

Takeaways

• Use withExpression (via staudenmeir/laravel-cte) for readable, composable CTEs.
• Filter on window function results by wrapping the windowed query as a subquery.
• LATERAL joins require a raw join expression; there is no first-class Laravel API for them.
• Isolate complex queries in dedicated classes and inject the connection for testability.
• Always run EXPLAIN (ANALYZE, BUFFERS) on non-trivial window queries — materialised CTEs can help or hurt depending on row counts.