PHP
Aggregating Related Data with Eloquent `withSum` and `withAvg`
Efficiently calculate sums, averages, maximums, or minimums of related model attributes and attach them to the parent model using Eloquent's `withSum`, `withAvg`, and similar methods.
use App\Models\User;
use App\Models\Product;
use App\Models\Order;
use App\Models\Review;
// Add a 'total_order_amount' attribute to each user based on their orders
$usersWithOrderSums = User::withSum('orders', 'amount')->get();
foreach ($usersWithOrderSums as $user) {
echo "User {$user->name} total orders: {$user->orders_sum_amount}
";
}
// Add an 'average_rating' attribute to each product based on its reviews
$productsWithAverageRating = Product::withAvg('reviews', 'rating')->get();
foreach ($productsWithAverageRating as $product) {
echo "Product {$product->name} average rating: {$product->reviews_avg_rating}
";
}
// You can also add conditions to the aggregate
$usersWithSpecificOrderSums = User::withSum(['orders as paid_orders_sum' => function ($query) {
$query->where('status', 'paid');
}], 'amount')->get();
foreach ($usersWithSpecificOrderSums as $user) {
echo "User {$user->name} total paid orders: {$user->paid_orders_sum}
";
}
How it works: Eloquent's `withSum`, `withAvg`, `withMax`, `withMin` are powerful methods for performing aggregate functions on related models and attaching the result directly as an attribute to the parent model. This avoids N+1 queries for calculating these aggregates. The resulting attribute is named using a convention (e.g., `relation_sum_column`). You can also apply conditions to the related model's query to calculate aggregates for specific subsets of related data, providing immense flexibility for reporting and data display.