PostgreSQL CTEs, Window Functions & Lateral Joins in Laravel | Mohamed Said        [  ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png)   Mohamed Said Laravel Backend Engineer  ](https://msaied.com) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles  ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) 

       [  ](https://github.com/EG-Mohamed)       

 [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) 

  [ home ](https://msaied.com)    [ articles ](https://msaied.com/articles)    PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel        On this page       1. [  PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel ](#postgresql-ctes-window-functions-and-lateral-joins-in-laravel)
2. [  Common Table Expressions (CTEs) ](#common-table-expressions-ctes)
3. [  Window Functions for Rankings and Running Totals ](#window-functions-for-rankings-and-running-totals)
4. [  LATERAL Joins for Per-Row Subqueries ](#lateral-joins-for-per-row-subqueries)
5. [  Keeping It Testable ](#keeping-it-testable)
6. [  Takeaways ](#takeaways)

  ![PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel](https://cdn.msaied.com/363/8e5685c14467b502c2bcbd62b4f47f64.png)

  #laravel   #postgresql   #query-builder   #performance  

 PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel 
=================================================================

     4 Jul 2026      3 min read    ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg)  Mohamed Said  

       Table of contents

1. [  01   PostgreSQL CTEs, Window Functions, and Lateral Joins in Laravel  ](#postgresql-ctes-window-functions-and-lateral-joins-in-laravel)
2. [  02   Common Table Expressions (CTEs)  ](#common-table-expressions-ctes)
3. [  03   Window Functions for Rankings and Running Totals  ](#window-functions-for-rankings-and-running-totals)
4. [  04   LATERAL Joins for Per-Row Subqueries  ](#lateral-joins-for-per-row-subqueries)
5. [  05   Keeping It Testable  ](#keeping-it-testable)
6. [  06   Takeaways  ](#takeaways)

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

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

```php
// 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', '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.

```php
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', '