PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel

Beyond Eloquent Basics: Advanced PostgreSQL in Laravel

Eloquent is excellent for CRUD, but analytical queries, rankings, and hierarchical data quickly push it to its limits. PostgreSQL's CTEs, window functions, and LATERAL joins are purpose-built for these cases. Laravel's query builder lets you drop into raw SQL fragments without abandoning the fluent interface entirely.

Common Table Expressions (CTEs)

A CTE names a subquery so you can reference it multiple times or build readable multi-step logic.

$results = DB::query()
    ->withExpression('ranked_orders', function ($query) {
        $query->from('orders')
            ->select('customer_id', 'total', 'created_at')
            ->where('status', 'completed');
    })
    ->from('ranked_orders')
    ->where('total', '>', 500)
    ->get();

withExpression is provided by the staudenmeir/laravel-cte package, which adds first-class CTE support to Laravel's query builder.

For recursive CTEs — think category trees or org charts — the same package exposes withRecursiveExpression:

$tree = DB::query()
    ->withRecursiveExpression('category_tree', function ($query) {
        // Anchor: root categories
        $query->from('categories')
            ->whereNull('parent_id')
            ->select('id', 'name', 'parent_id', DB::raw('0 as depth'))
            ->unionAll(
                // Recursive member
                DB::table('categories as c')
                    ->join('category_tree as ct', 'c.parent_id', '=', 'ct.id')
                    ->select('c.id', 'c.name', 'c.parent_id', DB::raw('ct.depth + 1'))
            );
    })
    ->from('category_tree')
    ->orderBy('depth')
    ->get();

This replaces multiple round-trips or application-side tree assembly with a single query.

Window Functions

Window functions compute values across a set of rows related to the current row — without collapsing them into groups.

Running totals and rankings

$rows = DB::table('orders')
    ->select(
        'customer_id',
        'total',
        'created_at',
        DB::raw('SUM(total) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total'),
        DB::raw('RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rank_by_value')
    )
    ->where('status', 'completed')
    ->get();

You get per-customer running totals and value rankings in one pass. The equivalent in PHP would require loading all rows, grouping them, and iterating — far more memory and time.

Lag/lead for time-series deltas

DB::table('daily_metrics')
    ->select(
        'date',
        'revenue',
        DB::raw("LAG(revenue) OVER (ORDER BY date) AS prev_revenue"),
        DB::raw("revenue - LAG(revenue) OVER (ORDER BY date) AS delta")
    )
    ->orderBy('date')
    ->get();

This is the idiomatic way to compute day-over-day changes without a self-join.

LATERAL Joins

A LATERAL join lets each row of the left table be referenced inside the right subquery — effectively a correlated subquery that returns a set of rows rather than a scalar.

Fetch the latest N rows per group

$customers = DB::table('customers as c')
    ->joinLateral(
        DB::table('orders')
            ->whereColumn('orders.customer_id', 'c.id')
            ->orderByDesc('created_at')
            ->limit(3)
            ->select('id as order_id', 'total', 'created_at'),
        'recent_orders'
    )
    ->select('c.id', 'c.name', 'recent_orders.*')
    ->get();

joinLateral was added to Laravel's query builder in Laravel 9.x. It compiles to JOIN LATERAL (...) ON TRUE, which PostgreSQL handles efficiently with an index on (customer_id, created_at DESC).

Compare this to the classic ROW_NUMBER() window-function approach — both work, but LATERAL is often more readable when the subquery is complex.

Combining All Three

Real analytical dashboards often chain all three techniques:

DB::query()
    ->withExpression('active_customers', fn($q) =>
        $q->from('customers')->where('active', true)
    )
    ->from('active_customers as ac')
    ->joinLateral(
        DB::table('orders')
            ->whereColumn('orders.customer_id', 'ac.id')
            ->select(
                'customer_id',
                DB::raw('SUM(total) AS lifetime_value'),
                DB::raw('RANK() OVER (ORDER BY SUM(total) DESC) AS value_rank')
            )
            ->groupBy('customer_id'),
        'stats'
    )
    ->select('ac.id', 'ac.name', 'stats.lifetime_value', 'stats.value_rank')
    ->orderBy('stats.value_rank')
    ->get();

Key Takeaways

• Use CTEs to name and reuse subqueries; use recursive CTEs for tree/graph traversal.
• Window functions compute rankings, running totals, and deltas in a single pass — no PHP-side aggregation needed.
• LATERAL joins replace "top N per group" patterns cleanly and are natively supported by joinLateral() in Laravel 9+.
• Keep raw SQL fragments inside DB::raw() or dedicated query builder methods; avoid embedding them in Eloquent model methods where they become invisible to static analysis.
• Always verify query plans with EXPLAIN (ANALYZE, BUFFERS) — CTEs in PostgreSQL 12+ are not always optimization fences, but complex ones can still surprise you.