Sort Users by Their Last Order Without an N+1: Eloquent Subquery Selects

The requirement was four words on a ticket: "Last ordered" column, sortable. The admin users table already paginated fine. Adding one timestamp from the most recent order took three rewrites before I landed on the Laravel Eloquent subquery select, which is the only one of the three that fits this shape of problem.

It is not eager loading.

Count the queries for all three approaches#

Before changing anything, render the same page three ways and write down the query count and the peak memory for each. The numbers are the argument, and the middle one is the one most teams have never actually looked at.

The first attempt is the obvious one, and it is the N+1 everybody recognises:

// 51 queries on a 50-row page: one for the users, one per row in Blade.
$users = User::paginate(50);

{{ $user->orders()->latest()->first()?->created_at?->diffForHumans() }}

The reflex fix is eager loading. It does drop the count to two, and if you are not already running Eloquent strict mode to catch N+1 queries early this is the point where the problem would normally have surfaced:

// 2 queries — but every order for all 50 users is now hydrated in memory.
$users = User::with('orders')->paginate(50);

Two queries, and a customer with four hundred orders means four hundred Order models built so Blade can read one created_at. The count looks healthy in Telescope, Debugbar or Pulse while memory quietly climbs. That is the trap: query count is not the only cost, and eager loading only optimises one of them.

The subquery select is one query and hydrates nothing extra:

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

// 1 query, one extra column, zero extra models.
$users = User::addSelect(['last_order_at' => Order::select('created_at')
    ->whereColumn('user_id', 'users.id')
    ->orderByDesc('created_at')
    ->limit(1),
])->paginate(50);

Add the Eloquent subquery select to the query#

Every piece of that subquery is load-bearing, and getting any one of them wrong produces an error message that does not obviously point back at the line you wrote. Walk it once and you will not have to debug it again.

select('created_at') must name exactly one column. A scalar subquery that returns two columns fails at the driver: MySQL says Operand should contain 1 column(s), PostgreSQL says the subquery must return only one column.

whereColumn('user_id', 'users.id') correlates the subquery to the current outer row. Using where('user_id', 'users.id') instead binds the literal string users.id as a parameter and silently matches nothing.

limit(1) is mandatory. Without it the database sees a scalar subquery that can return many rows and throws Subquery returns more than 1 row the moment any user has two orders.

orderByDesc('created_at') decides which row wins. If two orders can share a timestamp — and with second-resolution columns and bulk inserts they will — add a deterministic tiebreak with ->orderByDesc('id'), or the row you get back changes between runs.

The generated SQL on MySQL is exactly what you would have written by hand:

select `users`.*, (
    select `created_at` from `orders`
    where `user_id` = `users`.`id`
    order by `created_at` desc, `id` desc
    limit 1
) as `last_order_at`
from `users`
limit 50 offset 0

Note the users.*. Laravel adds it for you: addSelect() checks whether the query already has a select list and, when it does not and the value you passed is queryable, it calls select($this->from.'.*') before adding the sub-select. You only lose your columns if you narrowed the select list yourself first — more on that later.

Sort the paginated list by the subquery select#

Sorting is the requirement eager loading cannot satisfy at all, because the ordering has to happen in the database before LIMIT/OFFSET slices the page. Laravel's orderBy accepts a query builder for exactly this.

The documented form passes the subquery straight to orderByDesc:

$users = User::orderByDesc(
    Order::select('created_at')
        ->whereColumn('user_id', 'users.id')
        ->orderByDesc('created_at')
        ->limit(1)
)->paginate(50);

If you are already selecting the value, ordering by the alias is cheaper, because the subquery appears in the statement once instead of twice:

$users = User::addSelect(['last_order_at' => Order::select('created_at')
    ->whereColumn('user_id', 'users.id')
    ->orderByDesc('created_at')
    ->limit(1),
])
    ->orderByDesc('last_order_at')
    ->paginate(50);

That works on MySQL, PostgreSQL and SQLite. ORDER BY is evaluated after the select list, so output column aliases are in scope. What does not work anywhere is filtering on the alias — WHERE runs before SELECT, so ->where('last_order_at', '>', now()->subMonth()) fails on every driver. To filter, use whereHas('orders', fn ($query) => $query->where('created_at', '>', now()->subMonth())) and leave the subquery to do presentation only.

Pagination is fine either way. Laravel builds its count query by cloning the builder without columns, orders, limit and offset, so neither the sub-select nor the ordering reaches the count(*) — you are not paying for the subquery twice per page load.

Use withMax and withCount where they fit#

Most of the time you do not need a hand-rolled subquery at all, because Eloquent ships aggregate helpers that build one for you. Reach for the helper first and drop to the manual subquery only when the helper cannot express what you want.

Eloquent provides withCount, withMin, withMax, withAvg, withSum and withExists. Each one adds a {relation}_{function}_{column} attribute, and all of them accept an as alias:

$users = User::withMax('orders as last_order_at', 'created_at')
    ->withCount('orders')
    ->withSum('orders as lifetime_value', 'total')
    ->withExists('orders')
    ->paginate(50);

// $user->last_order_at, $user->orders_count, $user->lifetime_value, $user->orders_exists

All of them are thin wrappers over withAggregate($relations, $column, $function), which is public if you need to build the function name dynamically. One ordering rule applies to the whole family: if you are also calling select(), call the aggregate helpers after it, or your select list overwrites theirs.

Tell the latest value apart from the latest row#

This is the distinction that decides which tool you need, and it is worth being precise about because the two requirements sound identical when a stakeholder says them out loud.

"When did they last order?" is a latest value question. It is an aggregate over one column, and withMax('orders as last_order_at', 'created_at') answers it perfectly.

"What was the status of their last order?" is a latest row question. No aggregate can answer it. MAX(status) is alphabetically largest, not most recent, and that is a bug that will sit in production looking plausible for months.

It is tempting to try withAggregate('orders as last_order_status', 'status') with no function, since that path does append a LIMIT 1 sub-select. Read the implementation before you trust it — withAggregate sets $query->orders = null on the sub-select to keep the generated SQL valid, so any ordering you wanted is discarded and you get an arbitrary row. For a latest-row column, write the subquery yourself:

$users = User::addSelect([
    'last_order_at' => Order::select('created_at')
        ->whereColumn('user_id', 'users.id')
        ->orderByDesc('created_at')->orderByDesc('id')->limit(1),

    'last_order_status' => Order::select('status')
        ->whereColumn('user_id', 'users.id')
        ->orderByDesc('created_at')->orderByDesc('id')->limit(1),
])->paginate(50);

Two correlated subqueries is two index lookups per row. If you need more than about three columns off the same winning row, a LATERAL join (PostgreSQL, or MySQL 8.0.14 and later) does it in one pass and is worth the raw SQL.

Cast the aliased column at query time#

The aliased column arrives as whatever string the driver handed back. Eloquent has no idea it is a date, because $casts describes the model's own table and last_order_at is not on it. The first thing you will see is a fatal in Blade.

Call to a member function diffForHumans() on string

Adding it to the model's casts() works but is a lie about the schema — the column exists on some queries and not others. Cast it where you select it instead, with query-time casting:

$users = User::addSelect(['last_order_at' => Order::select('created_at')
    ->whereColumn('user_id', 'users.id')
    ->orderByDesc('created_at')
    ->limit(1),
])
    ->withCasts(['last_order_at' => 'datetime'])
    ->paginate(50);

withCasts() merges into the model's casts for that builder only. It works for the aggregate helpers too — withMax('orders as last_order_at', 'created_at') has the same problem and the same fix. The one exception is withExists, which applies a bool cast for you; every other helper hands back a raw driver value.

Wrap the pattern in a query scope#

Once the select and its cast have to stay in sync, they belong together in one place. A scope keeps the controller readable and gives you a single line to change when the column moves.

namespace App\Models;

use Illuminate\Database\Eloquent\Attributes\Scope;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    #[Scope]
    protected function withLastOrder(Builder $query): void
    {
        $query->addSelect([
            'last_order_at' => Order::select('created_at')
                ->whereColumn('user_id', 'users.id')
                ->orderByDesc('created_at')
                ->orderByDesc('id')
                ->limit(1),
        ])->withCasts(['last_order_at' => 'datetime']);
    }
}

The #[Scope] attribute is the Laravel 13 form; the old scopeWithLastOrder() naming convention still works if you prefer it. Either way the call site is now one word, which is what makes this usable inside a Livewire table with server-driven sorting:

public function render()
{
    return view('livewire.users-table', [
        'users' => User::query()
            ->withLastOrder()
            ->orderBy('last_order_at', $this->sortDirection)
            ->paginate(50),
    ]);
}

Filament v5 wires it up through the column's own sort closure, which sits naturally alongside table summarizers for column totals:

use Filament\Tables\Columns\TextColumn;
use Illuminate\Database\Eloquent\Builder;

TextColumn::make('last_order_at')
    ->label('Last ordered')
    ->dateTime()
    ->sortable(query: fn (Builder $query, string $direction): Builder => $query
        ->orderBy('last_order_at', $direction)),

Index the foreign key before you ship it#

A correlated subquery executes once per outer row. On a 50-row page that is 50 lookups into orders, and whether those are index seeks or table scans is the entire difference between this pattern being free and it being worse than the N+1 you replaced.

The index you need covers the correlation column first and the ordering column second:

Schema::table('orders', function (Blueprint $table) {
    $table->index(['user_id', 'created_at']);
});

Then look at the plan on your own data rather than trusting the shape of mine:

EXPLAIN ANALYZE
SELECT users.*, (
    SELECT created_at FROM orders
    WHERE user_id = users.id
    ORDER BY created_at DESC, id DESC
    LIMIT 1
) AS last_order_at
FROM users LIMIT 50;

On MySQL you want the subquery line to report DEPENDENT SUBQUERY using your new index, not type: ALL. On PostgreSQL you want an index scan under the SubPlan node, not a Seq Scan on orders. If you see a scan, the composite index is missing or the column order in it is wrong.

The heuristic I use: if the related table is small enough that a full scan is trivial and each parent has a handful of children, the eager load was already fine and this is premature. Once the related table is large, or a single parent can have thousands of children, the indexed subquery wins decisively. Neither answer is universal, which is why the plan matters more than the rule — the same discipline applies to profiling a slow endpoint properly rather than guessing at the bottleneck.

Work around the four gotchas that bite later#

Four things about this pattern surprise people weeks after shipping it, when the original context has gone. They are all cheap to avoid once you know they exist.

A narrowed select list stays narrow. Laravel only injects users.* when the query has no select list yet. Call ->select('id', 'name') first and addSelect(['last_order_at' => …]) appends to that, so the model comes back missing every other attribute. Add the columns you want explicitly, or let addSelect go first.

An aliased outer table breaks the correlation. whereColumn('user_id', 'users.id') hard-codes a table name. Inside a union or a builder that did from('users as u'), that reference no longer resolves. Match the alias — whereColumn('user_id', 'u.id') — or drop the aliasing.

Soft deletes apply, but only through the model. Passing an Eloquent builder to addSelect runs it through applyScopes() when the SQL is compiled, so Order::select(...) excludes trashed orders. A DB::table('orders') subquery does not — it will happily return a deleted order's timestamp. Pick one deliberately and leave a comment saying which, because the two read almost identically.

Null ordering flips between drivers. Users who have never ordered produce a NULL. On MySQL and SQLite, ORDER BY … DESC puts nulls last; on PostgreSQL it puts them first, because Postgres sorts nulls as larger than any value. Laravel has no nullsLast() helper, so be explicit when it matters:

// PostgreSQL
$query->orderByRaw('last_order_at desc nulls last');

// Portable across drivers
$query->orderByRaw('coalesce(last_order_at, ?) desc', ['1970-01-01 00:00:00']);

Wrapping Up#

Add the subquery, add the composite index, cast the alias with withCasts(), and wrap all three in a scope — then check the plan on production-sized data before you call it done. Reach for withMax when you only need the latest value, and keep the manual subquery for when you need a column off the latest row.

If the page is still slow after that, the next thing to look at is where the read is being served from. Routing reads to a replica without serving stale data helps when the primary is saturated, and a caching strategy with proper invalidation helps when the same admin table is being refreshed all day by the same five people.

FAQ#

How do I order Eloquent results by a column on a related table?

Pass a query builder to orderBy or orderByDesc. Laravel detects a queryable argument, compiles it to a correlated subquery and wraps it in parentheses inside the ORDER BY clause, so the sort happens in the database before pagination slices the page. If you are already selecting the same value with addSelect, order by the alias instead — ORDER BY is evaluated after the select list on MySQL, PostgreSQL and SQLite, and that way the subquery appears in the statement once rather than twice.

What is the difference between addSelect subquery and eager loading in Laravel?

Eager loading runs a second query and hydrates full model instances for every related row so you can traverse the relationship in PHP. An addSelect subquery adds one scalar column to the existing query and hydrates nothing extra. Both eliminate the N+1, but eager loading still pays the memory cost of every related record, and it cannot sort the parent list by a related column because the data does not exist until after pagination has already run.

How do I get the latest related record for each model without N+1?

Use addSelect with a correlated subquery that selects one column, constrains with whereColumn, orders by the timestamp descending and limits to one row. Repeat the subquery for each column you need from that row, since a scalar subquery can only return one. If you need more than about three columns off the same winning row, a LATERAL join on PostgreSQL or MySQL 8.0.14+ fetches them in a single pass instead.

Why is my subquery select column not cast to a Carbon date?

Because $casts describes the model's own table, and the aliased column is not on it — Eloquent has no way to know last_order_at is a datetime, so it hands back the raw driver string and ->diffForHumans() fatals. Fix it with query-time casting: chain ->withCasts(['last_order_at' => 'datetime']) onto the builder. The same applies to withMax and friends; withExists is the one exception, as it applies a boolean cast automatically.

When should I use withMax instead of a manual subquery?

Use withMax whenever the value you want is the aggregate — the newest timestamp, the highest score, the largest order total. It is shorter, it aliases cleanly with withMax('orders as last_order_at', 'created_at'), and Laravel builds the subquery for you. Drop to a manual addSelect subquery only when you need a different, non-aggregated column from the row that holds the maximum, which no aggregate function can express.

Does an Eloquent subquery select work with pagination?

Yes. Laravel builds its pagination count query by cloning the builder without its columns, orders, limit and offset, so neither the sub-select nor a subquery ordering is included in the count(*). That is a real advantage over the leftJoin plus groupBy approach, which duplicates parent rows and inflates the total the paginator reports. The one thing to watch is null handling: parents with no related rows sort to opposite ends on MySQL and PostgreSQL, so pin it down with orderByRaw if the first page has to look the same everywhere.