PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel

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:

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

$sql = <<<SQL
    WITH ranked_orders AS (
        SELECT user_id, total,
               RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS rnk
        FROM orders
    )
    SELECT * FROM ranked_orders WHERE rnk = 1
SQL;

$topOrders = DB::select($sql);

This is explicit, version-controlled, and trivially testable — pass it into a repository method and mock DB::select in tests.

Window Functions for Ranking and Running Totals

Window functions (ROW_NUMBER, RANK, SUM OVER, LAG) are impossible to express in Eloquent natively. Use DB::raw inside addSelect:

$results = DB::table('transactions')
    ->select('id', 'account_id', 'amount', 'created_at')
    ->addSelect(DB::raw(
        'SUM(amount) OVER (PARTITION BY account_id ORDER BY created_at) AS running_total'
    ))
    ->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:

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

$sql = <<<SQL
    SELECT u.id, u.email, lo.total, lo.created_at
    FROM users u
    CROSS JOIN LATERAL (
        SELECT total, created_at
        FROM orders
        WHERE user_id = u.id
        ORDER BY created_at DESC
        LIMIT 1
    ) lo
SQL;

$rows = DB::select($sql);

This pattern is dramatically faster than a correlated subquery on large tables — PostgreSQL can use an index scan per outer row.

Encapsulating Raw SQL Safely

Raw SQL in repositories is fine. The mistake is scattering it. Create a ReportingRepository or a dedicated query class:

class UserLifetimeValueQuery
{
    public function execute(int $limit = 100): Collection
    {
        return collect(DB::select(
            file_get_contents(resource_path('sql/user_lifetime_value.sql')),
            ['limit' => $limit]
        ));
    }
}

Storing complex SQL in resources/sql/ keeps it syntax-highlighted, diffable, and reviewable without PHP string noise.

Takeaways

• Use DB::select with named CTEs for complex multi-step aggregations — it's readable and testable.
• Window functions belong in addSelect(DB::raw(...)) and should be encapsulated in repository or query classes.
• CROSS JOIN LATERAL is the correct PostgreSQL pattern for latest-N-per-group; Laravel's joinSub doesn't emit LATERAL, so drop to raw SQL.
• Store non-trivial SQL in resources/sql/ files for diffability and IDE support.
• Always benchmark with EXPLAIN ANALYZE — CTEs in PostgreSQL 12+ are not optimization fences by default, but LATERAL subqueries can change plan shape significantly.