withCount and withSum Are Lying to You About Performance
Laravel's withCount and withSum feel like magic until you look at the SQL they generate. Here's what I use instead when it actually matters.
I spent an afternoon last year chasing a slow dashboard in a Laravel app I'd built for a print management client. The page loaded order counts, line item totals, and fulfillment summaries across a few thousand records. I'd used withCount and withSum everywhere because they're convenient and the docs make them look free. They're not.
This isn't a complaint about Eloquent. It's a heads-up about what those methods actually emit, and what to reach for when the query plan matters.
What the Docs Suggest
The Eloquent docs show something like this:
$orders = Order::withCount('lineItems')
->withSum('lineItems', 'total')
->get();
And you get line_items_count and line_items_sum_total on every model. Clean. Readable. The kind of thing that makes Laravel fun to write.
The problem is what this compiles into.
What the SQL Actually Looks Like
Run DB::enableQueryLog() or fire up Telescope and look at what gets sent:
SELECT
`orders`.*,
(
SELECT COUNT(*)
FROM `line_items`
WHERE `line_items`.`order_id` = `orders`.`id`
AND `line_items`.`deleted_at` IS NULL
) AS `line_items_count`,
(
SELECT SUM(`line_items`.`total`)
FROM `line_items`
WHERE `line_items`.`order_id` = `orders`.`id`
AND `line_items`.`deleted_at` IS NULL
) AS `line_items_sum_total`
FROM `orders`
Two correlated subqueries. One per aggregate, per row. If you're pulling 500 orders, MySQL is running 1,000 subquery executions inside the outer scan. Add a third aggregate and it's 1,500.
MySQL's optimizer can sometimes flatten these into something reasonable with a good index, but it's not guaranteed, and on complex schemas it usually doesn't. The EXPLAIN output on that print management dashboard showed "dependent subquery" on both - textbook correlated execution.
The Fix: Push It Into a Join
The thing I reach for instead is a single LEFT JOIN onto a grouped subquery. One pass over line_items, regardless of how many aggregates I need.
$orders = Order::query()
->leftJoinSub(
DB::table('line_items')
->selectRaw('order_id, COUNT(*) as line_items_count, SUM(total) as line_items_sum')
->whereNull('deleted_at')
->groupBy('order_id'),
'li_agg',
'li_agg.order_id',
'=',
'orders.id'
)
->select('orders.*', 'li_agg.line_items_count', 'li_agg.line_items_sum')
->get();
The SQL this produces:
SELECT
`orders`.*,
`li_agg`.`line_items_count`,
`li_agg`.`line_items_sum`
FROM `orders`
LEFT JOIN (
SELECT
`order_id`,
COUNT(*) AS `line_items_count`,
SUM(`total`) AS `line_items_sum`
FROM `line_items`
WHERE `deleted_at` IS NULL
GROUP BY `order_id`
) AS `li_agg` ON `li_agg`.`order_id` = `orders`.`id`
One scan of line_items. One join. Doesn't matter how many columns you pull out of li_agg - you've already paid the grouping cost once.
On that print job dashboard, switching to this pattern dropped the query from ~900ms to ~60ms. Same data, same indexes, different shape.
Wrapping It in a Scope
I don't want to write that join by hand every time I need it, so I'll put it in a local scope on the model:
// app/Models/Order.php
public function scopeWithLineItemAggregates(Builder $query): Builder
{
return $query->leftJoinSub(
DB::table('line_items')
->selectRaw('order_id, COUNT(*) as line_items_count, SUM(total) as line_items_sum')
->whereNull('deleted_at')
->groupBy('order_id'),
'li_agg',
'li_agg.order_id',
'=',
'orders.id'
)->addSelect('orders.*', 'li_agg.line_items_count', 'li_agg.line_items_sum');
}
Then at the call site:
$orders = Order::withLineItemAggregates()->paginate(50);
Reads just as clean as withCount. Performs like a real query.
The Gotchas I Hit
Pagination breaks if you forget orders.*. When you bring in a join, Eloquent's select * becomes ambiguous. If you don't explicitly scope your select to orders.*, you'll sometimes get column collisions - especially if line_items has an id column that stomps on orders.id. Always be explicit.
withCount with constraints is trickier to replace. If you're using something like:
Order::withCount(['lineItems as pending_count' => fn($q) => $q->where('status', 'pending')])
You can still handle this in the grouped subquery with a conditional aggregate:
DB::table('line_items')
->selectRaw("
order_id,
COUNT(*) as line_items_count,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_count
")
->whereNull('deleted_at')
->groupBy('order_id')
SQLs CASE WHEN inside an aggregate is exactly the right tool here and it stays inside the single pass.
Left join means nulls on orders with no line items. If an order has zero line items, li_agg won't have a row for it, so line_items_count comes back as null, not 0. Cast it at the select level:
->selectRaw('orders.*, COALESCE(li_agg.line_items_count, 0) as line_items_count')
Or cast the attribute on the model itself. Either way, don't let that null sneak into your view layer.
Global scopes can double-dip. If LineItem has a global scope (soft deletes, tenant scoping), it won't apply inside a raw DB::table() call. You have to replicate those conditions manually in the subquery. This is annoying, but it's also the honest version of what withCount was doing silently - at least now you can see it.
When withCount Is Fine
I don't throw withCount out entirely. It's fine when:
- You're counting a small result set, say under 100-200 rows, and you're not on a hot path.
- You're in a quick admin panel or internal tool where 400ms is acceptable.
- You only have one aggregate and the relationship table is small.
- You need a one-liner in a prototype and you'll come back to optimize.
The correlated subquery pattern that withCount generates isn't wrong, it's just not free. MySQL can handle it fine at modest scale with solid indexes. The footgun is assuming it scales linearly. It doesn't.
I've integrated enough reporting dashboards across healthcare clients, e-commerce shops, and industrial inventory systems to know that this is exactly where "worked fine in staging" turns into a 3am PagerDuty call after the client's data grows by a factor of ten.
When I'd Reach for Something Else Entirely
If aggregates are core to your domain and you're querying them constantly - think a dashboard that updates on every page load across millions of rows - the join pattern above is still going to do real work on every request. At that point I'd look at:
- Storing denormalized counts/sums in the parent table and updating them via observers or events. Cheap to read, pay the cost on write.
- A materialized view (Postgres does these natively, MySQL 8 can approximate with a generated table). Set it, refresh it on a schedule, query it like a table.
- Redis counters for things that need to be real-time, like a shopping cart item count.
But those are bigger architectural moves. For 80% of what I build, the leftJoinSub pattern hits the sweet spot - SQL that's honest about what it's doing, stays inside Eloquent, and doesn't require a schema change.
Closing
withCount and withSum are good APIs with a leaky abstraction. The convenience is real, but so is the correlated subquery hiding underneath. Look at your query log before you assume Eloquent is being clever on your behalf - it usually isn't.
Need help shipping something like this? Get in touch.