PostgreSQL CTEs, Recursive Queries, and Lateral Joins in Laravel

PostgreSQL CTEs, Recursive Queries, and Lateral Joins in Laravel

Eloquent handles 90% of your queries elegantly. The remaining 10% — hierarchical data, ranked results, correlated subqueries — is where raw PostgreSQL features pay dividends. Laravel's query builder gives you enough surface area to use them without abandoning the framework entirely.

Common Table Expressions (CTEs) with withExpression

Laravel 8+ ships with DB::query()->withExpression() via the staudenmeir/laravel-cte package, but for pure PostgreSQL you can also drop into DB::statement or use selectRaw with a leading WITH block. The cleaner approach is the package:

composer require staudenmeir/laravel-cte

use Illuminate\Support\Facades\DB;

$results = DB::table('orders')
    ->withExpression('ranked_orders', function ($query) {
        $query->from('orders')
            ->select([
                'id',
                'user_id',
                'total',
                DB::raw('RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS rnk'),
            ]);
    })
    ->from('ranked_orders')
    ->where('rnk', 1)
    ->get();

This pulls the single highest-value order per user in one round-trip. The CTE keeps the window function isolated, making the outer query readable and the execution plan efficient — PostgreSQL materialises the CTE once.

Recursive CTEs for Tree Traversal

Category trees, org charts, threaded comments — all map naturally to a recursive CTE. Eloquent has no native support, but a raw expression works cleanly:

$categoryId = 5;

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

        UNION ALL

        SELECT c.id, c.parent_id, c.name, 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,
    [$categoryId]
);

The anchor member seeds the recursion; the recursive member joins back to the CTE itself. PostgreSQL handles cycle detection internally when you add CYCLE id SET is_cycle USING path (PG 14+), preventing infinite loops on corrupt data.

Wrap this in a repository method and return a typed collection:

public function descendants(int $rootId): Collection
{
    $rows = DB::select(/* ... */, [$rootId]);
    return collect($rows)->map(fn($r) => CategoryDTO::fromStdClass($r));
}

LATERAL Joins for "Top N per Group"

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.

$topPosts = DB::table('users')
    ->join(
        DB::raw('LATERAL (
            SELECT id, title, published_at
            FROM posts
            WHERE posts.user_id = users.id
            ORDER BY published_at DESC
            LIMIT 3
        ) AS recent_posts'),
        DB::raw('TRUE'),
        '=',
        DB::raw('TRUE')
    )
    ->select(['users.id AS user_id', 'users.name', 'recent_posts.*'])
    ->get();

The ON TRUE trick is idiomatic for CROSS JOIN LATERAL semantics when you want all users regardless of whether they have posts. Switch to JOIN LATERAL ... ON TRUE and add WHERE recent_posts.id IS NOT NULL to filter users with no posts.

Keeping It Testable

Raw SQL in repositories is fine as long as it's behind an interface. Test with a real PostgreSQL database in your Pest suite — SQLite won't execute WITH RECURSIVE or LATERAL:

uses(RefreshDatabase::class);

it('returns descendants in depth order', function () {
    $root = Category::factory()->create();
    $child = Category::factory()->for($root, 'parent')->create();

    $results = app(CategoryRepository::class)->descendants($root->id);

    expect($results)->toHaveCount(2)
        ->and($results->first()->id)->toBe($root->id);
});

Set DB_CONNECTION=pgsql in phpunit.xml for the test suite and spin up a throwaway Postgres container in CI.

Takeaways

• CTEs keep complex subqueries composable and readable; use staudenmeir/laravel-cte for builder integration.
• Recursive CTEs are the idiomatic PostgreSQL solution for hierarchical data — avoid application-side tree walking.
• LATERAL joins replace multiple queries for "top N per group" patterns with a single, plan-friendly statement.
• Raw SQL in repositories is acceptable; hide it behind interfaces and test against a real PostgreSQL instance.
• Never assume SQLite parity — advanced PostgreSQL features require a real Postgres environment in CI.