How correlated subqueries works in Laravel

High-Speed Group Aggregations in Laravel Without the N+1 Penalty

A common database problem that developers face while building dashboards, e-commerce stores or SaaS applications is the "Greatest per Group" or "Latest per Group" query. Common real world examples involve getting each user's latest login, each customer's most recent order, or the last message of dozens of conversation threads.

Solving this in Laravel leads developers down some common pathways: using with('orders') and selecting ->orders->first() in PHP (which burns huge amounts of memory by loading thousands of irrelevant rows), or writing looping logic in their controllers leading to thousands of N+1 query bottlenecks.

The best solution is to use Correlated Subqueries directly in Eloquent using addSelect(), and hasOne()->ofMany() style relationships. In this tutorial we'll learn about how correlated subqueries work in Laravel, how legacy raw queries compare to new Eloquent approaches, and how to fetch latest-per-group records in single blazing fast database queries.

The Problem: Why Traditional Approaches Fail at Scale

Let's take a realistic scenario: You need to display a dashboard table of 50 users along with their most recent order (including the order date and total amount).

Approach 1: The N+1 Query Trap

$users = User::all();

foreach ($users as $user) {
    // Triggers 1 additional SQL query for EVERY user (50 users = 51 queries!)
    $latestOrder = $user->orders()->latest('created_at')->first();
}

Approach 2: The Eager Loading Memory Trap

// Loads EVERY order in the database for these users into memory
$users = User::with('orders')->get();

foreach ($users as $user) {
    // Picks the first one in PHP, but thousands of old orders were needlessly hydrated
    $latestOrder = $user->orders->sortByDesc('created_at')->first();
}

If each user has 500 orders, fetching 50 users hydrates 25,000 Eloquent model instances into memory just to read 50 records.

Method 1: The Modern Way using ofMany() / latestOfMany()

Since modern Laravel versions, Eloquent has a first-party solution to implement such relationships without writing any raw SQL queries — the ofMany() and latestOfMany() relationship methods

Behind the scenes, Laravel uses an optimized correlated subquery join to fetch the latest record for each parent model.

Defining latestOfMany() on the Model

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasOne;
use Illuminate\Database\Eloquent\Relations\HasMany;

class User extends Model
{
    // Standard 1:N relationship.
    public function orders(): HasMany
    {
        return $this->hasMany(Order::class);
    }

    // Correlated subquery relation: Get only the user's latest order.
    public function latestOrder(): HasOne
    {
        return $this->hasOne(Order::class)->latestOfMany();
    }

    // Get the user's largest order (custom criteria).
    public function largestOrder(): HasOne
    {
        return $this->hasOne(Order::class)->ofMany('amount', 'max');
    }
}

Eager Loading Without Bottlenecks

Now, eager loading the latest order runs in just 2 fast queries, hydrating exactly 50 order models regardless of how many hundreds of thousands of historical orders exist:

// Runs exactly 2 SQL queries!
$users = User::with('latestOrder')->paginate(50);

foreach ($users as $user) {
    echo $user->name . ' - Last Order Total: ' . $user->latestOrder?->amount;
}

Here is what the underlying SQL that Laravel executes looks like:

-- Query 1: Fetch users
SELECT * FROM `users` LIMIT 50;

-- Query 2: Correlated inner join fetching only the latest order ID per user
SELECT `orders`.* FROM `orders`
INNER JOIN (
    SELECT MAX(`id`) AS `id_aggregate`, `user_id` 
    FROM `orders` 
    WHERE `user_id` IN (1, 2, 3, ...) 
    GROUP BY `user_id`
) AS `latest_orders` 
ON `orders`.`id` = `latest_orders`.`id_aggregate` 
AND `orders`.`user_id` = `latest_orders`.`user_id`;

Method 2: Select Subqueries Using addSelect()

What if you only need a single column from the latest related record—such as the last_login_at timestamp or the latest order amount—and you don't even need to hydrate the full related Order model?

Eloquent allows you to pass a subquery directly into addSelect() or select():

namespace App\Http\Controllers;

use App\Models\User;
use App\Models\Order;

class UserController extends Controller
{
    public function index()
    {
        $users = User::query()
            ->addSelect([
                // Correlated subquery selecting a single value from the related table
                'last_order_date' => Order::select('created_at')
                    ->whereColumn('orders.user_id', 'users.id')
                    ->latest()
                    ->take(1),

                'last_order_amount' => Order::select('amount')
                    ->whereColumn('orders.user_id', 'users.id')
                    ->latest()
                    ->take(1),
            ])
            ->paginate(20);

        return view('users.index', compact('users'));
    }
}

Let's understand what is happening here:

whereColumn('orders.user_id', 'users.id') creates the correlation between the outer users query and the inner orders subquery.

latest()->take(1) limits the subquery to return only the single most recent value.

The returned values are automatically hydrated into the User model as dynamic attributes (e.g. $user->last_order_date and $user->last_order_amount).

This runs in a single SQL query with virtually zero memory overhead.

Sorting by a Related Subquery Value

A huge advantage of correlated subqueries with addSelect() or orderBy() is that you can sort parent records based on a value inside a child relationship—something standard eager loading cannot do.

For example, sorting users by their most recent order date:

use App\Models\User;
use App\Models\Order;

$users = User::query()
    ->orderByDesc(
        Order::select('created_at')
            ->whereColumn('orders.user_id', 'users.id')
            ->latest()
            ->take(1)
    )
    ->paginate(20);

Users who placed orders most recently appear at the top of the list, executed completely at the database level.

Performance Comparison

Technique	SQL Queries	Memory Usage	Hydrated Models
In-Loop Query (N+1)	$N + 1$ (High)	Low	1 per parent
Full `with('orders')`	2	Massive (Hydrates every order)	Every historical record
`latestOfMany()`	2	Optimal	Exactly 1 per parent
`addSelect()` Subquery	1 (Single Query)	Lowest (Zero extra models)	0 extra models (raw attributes)

Things to Remember and Best Practices

📌 Composite Database Indexes are Essential: A correlated subquery runs against the child table for each row in the parent set. Ensure your foreign key and sorting column have a composite index (e.g. $table->index(['user_id', 'created_at'])). Without this index, your database engine will perform full table scans.

💡 Use withCasts() for Subquery Attributes: When fetching timestamps or numbers via addSelect() subqueries, Eloquent returns them as raw strings. You can automatically cast subquery results using Laravel's withCasts():
```
$users = User::addSelect(['last_order_date' => ...])->withCasts(['last_order_date' => 'datetime'])->get();
```
⚠️ Always Limit to 1 in Subquery Selects: A subquery used in a SELECT clause can only return a single scalar value. Always append ->latest()->take(1) or ->limit(1). If multiple rows are returned, SQL engines will throw a Subquery returns more than 1 row error.

Conclusion

The pattern of fetching latest per group is one of the most common operations in database driven applications. You can optimize your code, eliminate memory intensive eager loading and n+1 query loops, and keep your Eloquent queries lean, mean and performant by using latestOfMany() and correlated addSelect() subqueries.

Thank you for reading this article 😊

For any query, do not hesitate to comment 💬