PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel

Why Raw SQL Isn't a Dirty Word

Eloquent is excellent for CRUD. The moment you need ranked results per group, running totals, or hierarchical data, you are fighting the ORM instead of using the database. PostgreSQL has had CTEs, window functions, and LATERAL joins for years. Laravel's query builder gives you enough surface area to use them cleanly — no raw string soup required.

Common Table Expressions (CTEs)

Laravel 9+ ships with withExpression() via the DB facade when you pull in LaravelQueryEnumerations — but the cleanest path is DB::statement for one-offs or a custom macro for reuse.

For a reporting query that needs a CTE, use fromSub combined with a raw WITH prefix:

$ranked = DB::select(<<<SQL
    WITH monthly_revenue AS (
        SELECT
            customer_id,
            DATE_TRUNC('month', created_at) AS month,
            SUM(amount_cents) AS total
        FROM orders
        WHERE status = 'paid'
        GROUP BY 1, 2
    )
    SELECT
        customer_id,
        month,
        total,
        RANK() OVER (PARTITION BY month ORDER BY total DESC) AS revenue_rank
    FROM monthly_revenue
    ORDER BY month DESC, revenue_rank
SQL);

This is readable, version-controlled, and testable. Wrap it in a dedicated RevenueRankingQuery class with a get(): Collection method so callers never touch raw SQL.

Window Functions for Running Totals and Rankings

Suppose you need a running balance per account. A SUM(...) OVER (PARTITION BY ... ORDER BY ...) is the right tool:

class RunningBalanceQuery
{
    public function forAccount(int $accountId): Collection
    {
        $rows = DB::select(<<<SQL
            SELECT
                id,
                created_at,
                amount_cents,
                SUM(amount_cents) OVER (
                    PARTITION BY account_id
                    ORDER BY created_at
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                ) AS running_balance
            FROM transactions
            WHERE account_id = ?
            ORDER BY created_at
        SQL, [$accountId]);

        return collect($rows)->map(
            fn ($row) => new TransactionWithBalance(
                id: $row->id,
                amountCents: $row->amount_cents,
                runningBalance: $row->running_balance,
                createdAt: Carbon::parse($row->created_at),
            )
        );
    }
}

Mapping raw stdClass objects to typed DTOs immediately keeps the rest of your codebase type-safe.

LATERAL Joins for "Top N Per Group"

LATERAL is PostgreSQL's answer to correlated subqueries that return multiple rows. Fetching the three most recent orders per customer is a classic use case:

$results = DB::select(<<<SQL
    SELECT
        c.id AS customer_id,
        c.email,
        recent.id AS order_id,
        recent.total_cents,
        recent.created_at
    FROM customers c
    CROSS JOIN LATERAL (
        SELECT id, total_cents, created_at
        FROM orders
        WHERE customer_id = c.id
        ORDER BY created_at DESC
        LIMIT 3
    ) AS recent
    WHERE c.active = true
    ORDER BY c.id, recent.created_at DESC
SQL);

This runs a single query. The equivalent Eloquent approach — eager-loading all orders then slicing in PHP — pulls thousands of rows into memory for large datasets.

Recursive CTEs for Hierarchical Data

Category trees, org charts, threaded comments: recursive CTEs handle them all.

$tree = DB::select(<<<SQL
    WITH RECURSIVE category_tree AS (
        SELECT id, name, parent_id, 0 AS depth
        FROM categories
        WHERE parent_id IS NULL

        UNION ALL

        SELECT c.id, c.name, c.parent_id, ct.depth + 1
        FROM categories c
        INNER JOIN category_tree ct ON ct.id = c.parent_id
    )
    SELECT * FROM category_tree ORDER BY depth, name
SQL);

Add a max_depth guard (WHERE depth < 10) to prevent runaway recursion on corrupt data.

Keeping It Maintainable

• Encapsulate every complex query in a dedicated query class under App\Queries\.
• Test with a real PostgreSQL instance in Pest — SQLite won't execute these constructs.
• Use DB::select() + DTO mapping rather than hydrating Eloquent models from window-function results; the model lifecycle adds overhead you don't need.
• Add EXPLAIN ANALYZE assertions in staging: a misconfigured index turns a 5 ms CTE into a sequential scan.

Takeaways

• CTEs improve readability and allow query reuse within a single statement.
• Window functions (RANK, SUM OVER, ROW_NUMBER) eliminate PHP-side aggregation loops.
• LATERAL joins are the correct tool for top-N-per-group without subquery hacks.
• Recursive CTEs replace multiple round-trips for tree traversal.
• Wrap all advanced SQL in typed query classes and map results to DTOs immediately.