Every ERP or CRM has a customer list that people open fifty times a day. Name, number of orders, open balance, last order date. It looks like the simplest screen in the app.
Then the customer table grows to a few thousand rows, someone asks to sort by open balance, and the page that used to load instantly now takes four seconds. You open the query log and see the classic pattern: one query for the page of customers, then three more queries for every customer on that page.
This post is about fixing that screen properly with tools Eloquent already gives you: withCount, withSum, subquery selects, and subquery ordering. Everything below is from the Laravel 13 docs, and I ran the examples against a real database before writing about them.
The problem: aggregates computed in PHP
Here is the version most of us wrote at some point:
<?php
$customers = Customer::orderBy('name')->paginate(50);
foreach ($customers as $customer) {
$customer->order_count = $customer->orders()->count();
$customer->open_balance = $customer->orders()->where('status', 'open')->sum('total');
$customer->last_order_at = $customer->orders()->max('ordered_at');
}
Three queries per row. With 50 rows per page, that is 151 queries for one screen. Worse, you cannot sort or filter by open balance, because the number does not exist until PHP has looped over the page. Sorting the whole customer list by balance would mean loading every customer.
Eager loading (with('orders')) does not help much either. It reduces the query count, but now you are hydrating every order of every customer on the page just to add up one column.
What we want is for the database to do the counting and summing, in the same query that loads the customers.
Step 1: Counts with withCount
withCount adds a {relation}_count attribute to each model, computed as a correlated subquery:
<?php
use App\Models\Customer;
$customers = Customer::withCount('orders')->paginate(50);
foreach ($customers as $customer) {
echo $customer->orders_count;
}
You can add constraints and give each count its own name with an as alias, which means you can count the same relation several ways:
<?php
use Illuminate\Database\Eloquent\Builder;
$customers = Customer::withCount([
'orders',
'orders as open_orders_count' => fn (Builder $q) => $q->where('status', 'open'),
])->paginate(50);
Step 2: Sums, minimums and maximums
The same idea works for other aggregates: withSum, withMin, withMax, withAvg and withExists. The attribute name follows the pattern {relation}_{function}_{column}, so withSum('orders', 'total') gives you orders_sum_total. An alias gives it a name that reads better in the view:
<?php
use Illuminate\Database\Eloquent\Builder;
$customers = Customer::query()
->select(['id', 'name'])
->withCount('orders')
->withSum(
['orders as open_balance' => fn (Builder $q) => $q->where('status', 'open')],
'total'
)
->paginate(50);
Note the order: select() first, aggregates after. The docs call this out explicitly, and I will show below what happens if you get it wrong.
Step 3: "Last order" with a subquery select
withMax('orders', 'ordered_at') gives you the date of the last order. But often the screen needs something else from that last order: its number, its status, its sales rep. For that, Eloquent supports subquery selects through addSelect:
<?php
use App\Models\Order;
$customers = Customer::query()
->select(['id', 'name'])
->withCount('orders')
->addSelect(['last_order_number' => Order::select('number')
->whereColumn('customer_id', 'customers.id')
->latest('ordered_at')
->limit(1),
])
->paginate(50);
whereColumn ties the subquery to the outer row, latest('ordered_at') orders it, and limit(1) keeps it to a single value. The result is still one query for the whole page.
If you need the full last-order model rather than one column, a latestOfMany relationship (hasOne(Order::class)->latestOfMany('ordered_at')) with eager loading is the cleaner tool. Use subquery selects when you only need one or two values.
Step 4: Sort by the aggregate
Because the aggregate is now a column in the result set, sorting by it is one line:
<?php
$customers = Customer::query()
->select(['id', 'name'])
->withSum(['orders as open_balance' => fn ($q) => $q->where('status', 'open')], 'total')
->orderByDesc('open_balance')
->paginate(50);
And Eloquent's subquery ordering lets you sort by a related value without selecting it at all:
<?php
use App\Models\Order;
$customers = Customer::orderByDesc(
Order::select('ordered_at')
->whereColumn('customer_id', 'customers.id')
->latest('ordered_at')
->limit(1)
)->paginate(50);
That is "customers who ordered most recently first", computed by the database, with normal pagination.
What it actually runs
Here is the SQL from my test (SQLite, Laravel 13) for a customer list with an order count, an open-order count, an open balance and a last order date:
select "id", "name",
(select count(*) from "orders"
where "customers"."id" = "orders"."customer_id"
and "orders"."deleted_at" is null) as "orders_count",
(select count(*) from "orders"
where "customers"."id" = "orders"."customer_id"
and "status" = ? and "orders"."deleted_at" is null) as "open_orders_count",
(select sum("orders"."total") from "orders"
where "customers"."id" = "orders"."customer_id"
and "status" = ? and "orders"."deleted_at" is null) as "open_balance",
(select "ordered_at" from "orders"
where "customer_id" = "customers"."id" and "orders"."deleted_at" is null
order by "ordered_at" desc limit 1) as "last_order_at"
from "customers"
order by "open_balance" desc
One query. Notice also that every subquery got "orders"."deleted_at" is null for free. The Order model uses SoftDeletes, and the aggregates respect the model's global scopes, so a deleted order does not inflate anyone's balance.
Each of those subqueries runs per customer row, so give them an index to land on. For the queries above, a composite index on orders (customer_id, status) covers the filtered aggregates, and (customer_id, ordered_at) helps the "last order" lookup.
Common pitfalls
1. Calling select() after the aggregate. select() replaces the column list, including the subqueries withCount added. In my test, Customer::withCount('orders')->select(['id', 'name'])->first() returned orders_count as null. No error, just a missing number. Always select() first, then withCount / withSum / addSelect.
2. withSum returns null, not 0. SUM over zero rows is NULL in SQL. A customer with no open orders gets open_balance = null, and your view prints an empty cell or your number_format() call complains. If you want a zero, put COALESCE in a subquery select:
<?php
use App\Models\Order;
$customers = Customer::query()
->select(['id', 'name'])
->addSelect(['open_balance' => Order::selectRaw('coalesce(sum(total), 0)')
->whereColumn('customer_id', 'customers.id')
->where('status', 'open'),
])
->paginate(50);
3. NULLs change your sort order between databases. This one is easy to miss. With orderByDesc('open_balance'), MySQL and SQLite put NULL values last, but PostgreSQL puts them first by default. Your local SQLite tests pass, and production shows customers with no balance at the top of the "biggest balances" list. The COALESCE version above avoids the question entirely.
4. Filtering with where on the alias. Customer::withCount('orders')->where('orders_count', '>=', 5) looks natural, but WHERE cannot see column aliases in MySQL or PostgreSQL, so the query fails there. (SQLite happens to accept it, which is exactly why this bug survives local tests.) Filter with relationship existence queries instead:
<?php
// Customers with at least 5 orders
Customer::has('orders', '>=', 5)->get();
// Customers with at least one open order
Customer::whereHas('orders', fn ($q) => $q->where('status', 'open'))->get();
5. Decimal sums come back as strings or floats. The type of an aggregate depends on the driver. With DECIMAL columns on MySQL, expect a string like "120.75". On SQLite I got a PHP float. Never do money arithmetic on the float; keep it as a string or convert to integer cents before adding things up.
6. The count and the filter disagree. If the "Open orders" column uses where('status', 'open') and the "Has open orders" filter uses whereIn('status', ['open', 'partial']), users will find customers who match the filter but show 0 open orders. Put the definition of "open" in one place, such as a local scope on Order, and reuse it in both closures.
Summary
- Do not compute list-screen aggregates in a PHP loop; that is N queries per page and makes sorting impossible.
- Use
withCount,withSum,withMaxand friends, withasaliases and closure constraints, to get counts and totals in the same query. - Use
addSelectwith a correlated subquery when you need a single value from a related row, and subqueryorderByto sort by it. - Call
select()before any aggregate, handleNULLsums withCOALESCE, and filter withhas/whereHasinstead ofwhereon an alias. - Index the foreign key plus the columns your aggregates filter and sort on.
What is the slowest list screen in your app right now, and is it slow because of the data or because of the loop?
Top comments (0)