Model::withAggregate()

Overview

withAggregate() is the engine behind withCount(), withSum(), withAvg(), withMin(), and withMax(). It adds a subquery-based aggregate as a virtual attribute to your models, avoiding the need to load entire relationships just to compute a number.

Usage

The shorthand helpers cover most cases:

$authors = Author::query()
    ->withCount('books')
    ->withAvg('books', 'rating')
    ->withSum('books', 'pages')
    ->withMax('books', 'published_at')
    ->get();

$authors->first()->books_count;              // 12
$authors->first()->books_avg_rating;          // 4.2
$authors->first()->books_sum_pages;           // 3847
$authors->first()->books_max_published_at;    // '2024-03-15'

For custom aggregates, use withAggregate() directly:

$users = User::query()
    ->withAggregate('orders', 'total', 'sum')
    ->withAggregate('orders as latest_order', 'created_at', 'max')
    ->get();

$users->first()->orders_sum_total;    // 15430
$users->first()->latest_order;        // '2024-06-01 14:32:00'

Constrained Aggregates

Add conditions to the aggregate subquery:

$users = User::query()
    ->withCount(['orders' => function ($query) {
        $query->where('status', 'completed');
    }])
    ->withSum(['orders as refunded_total' => function ($query) {
        $query->where('status', 'refunded');
    }], 'total')
    ->get();

Sorting by Aggregate

Order by the aggregated value directly:

$categories = Category::query()
    ->withCount('products')
    ->orderByDesc('products_count')
    ->get();

When to Use

• Displaying counts, totals, or averages alongside parent models
• Sorting or filtering by related model aggregates
• Dashboard queries that need stats without loading full relationships
• Replacing manual DB::raw() subqueries with clean Eloquent syntax