Aggregate Queries Without N+1: withCount, withSum, withAvg, and withExists

A dashboard listing orders with their item count and total is one of the most common places an N+1 sneaks into a Laravel app, precisely because it looks harmless:

$orders = Order::latest()->take(20)->get();

// In the Blade view:
@foreach ($orders as $order)
    {{ $order->items->count() }} items, {{ $order->items->sum('price') }} total
@endforeach

That's 20 orders plus 40 additional queries — one to count items, one to sum prices, for every single order in the list. Eloquent has purpose-built helpers that fold every one of those into the original query.

withCount()

$orders = Order::withCount('items')->latest()->take(20)->get();

$orders[0]->items_count; // int, no extra query

withCount() adds a (SELECT COUNT(*) FROM order_items WHERE order_items.order_id = orders.id) as items_count subquery to the original SELECT. One query, no matter how many orders are in the result set. You can count with a constraint too:

Order::withCount(['items as pending_items_count' => function ($query) {
    $query->where('status', 'pending');
}])->get();

withSum(), withAvg(), withMin(), withMax()

The same pattern extends to every common aggregate:

Order::withSum('items', 'price')   // items_sum_price
    ->withAvg('items', 'price')     // items_avg_price
    ->withMin('items', 'price')     // items_min_price
    ->withMax('items', 'price')     // items_max_price
    ->get();

Each one generates its own correlated subquery column, and Eloquent casts the result to the correct numeric type automatically — a withSum() on a decimal column comes back as a string that behaves like Eloquent's other decimal attributes, not a lossy float.

withExists()

For a plain "does this have any at all" check, withExists() avoids even a COUNT, using an EXISTS subquery that the database can short-circuit on the first match:

Order::withExists('refunds')->get();

$order->refunds_exists; // bool

This is the correct replacement for $order->refunds->isNotEmpty() or $order->refunds()->exists() inside a loop — both trigger a query (or a full relationship load) per row, where withExists() folds the check into the original query as a boolean column.

Renaming the generated columns

The default {relation}_{aggregate}_{column} naming gets unwieldy fast. Use the as syntax to control it:

Order::withCount('items as item_count')
    ->withSum('items as items_total', 'price')
    ->get();

When to reach for a real join instead

These helpers are subqueries, not joins — great for "attach one aggregate value per row" but not a replacement for groupBy() reporting queries that need to aggregate across the whole table. If you're building a report ("total revenue per day across all orders"), a direct select(DB::raw(...))->groupBy() query is still the right tool. withCount() and its siblings are specifically for enriching a list of models with per-row aggregate data without falling into N+1.