PHP
Aggregating Related Data with `withSum`, `withAvg`, `withMax`
Efficiently aggregate data from related models using Eloquent's `withSum`, `withAvg`, and `withMax` methods, retrieving calculated values without loading entire relationships.
<?php
// app/Models/User.php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class User extends Model
{
public function orders()
{
return $this->hasMany(Order::class);
}
public function products()
{
return $this->hasMany(Product::class);
}
}
// app/Models/Order.php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Order extends Model
{
public function user()
{
return $this->belongsTo(User::class);
}
}
// app/Models/Product.php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Product extends Model
{
public function user()
{
return $this->belongsTo(User::class);
}
}
// Usage Example
// Get users with their total order amount
$usersWithTotalOrders = User::withSum('orders', 'amount')->get();
foreach ($usersWithTotalOrders as $user) {
echo "User {$user->name} has total order amount: {$user->orders_sum_amount}
";
}
// Get users with the average price of their products
$usersWithAvgProductPrice = User::withAvg('products', 'price')->get();
foreach ($usersWithAvgProductPrice as $user) {
echo "User {$user->name} has average product price: {$user->products_avg_price}
";
}
// Get users with the maximum order amount
$usersWithMaxOrder = User::withMax('orders', 'amount')->get();
foreach ($usersWithMaxOrder as $user) {
echo "User {$user->name} has max order amount: {$user->orders_max_amount}
";
}
// You can also add constraints to the aggregation
$usersWithLargeOrdersSum = User::withSum(['orders as big_orders_sum' => function ($query) {
$query->where('amount', '>', 100);
}], 'amount')->get();
How it works: Eloquent's `withSum`, `withAvg`, `withMax`, and `withMin` methods provide an efficient way to aggregate data from a related model and add the result as a convenient attribute to the parent model. Unlike loading the entire relationship and then calculating, these methods perform the aggregation directly in the database query. This significantly reduces the amount of data transferred and processed in PHP. The aggregated value is available on the parent model using a convention like `[relationship_name]_[aggregation_type]_[column_name]` (e.g., `orders_sum_amount`). You can also pass a closure to add constraints to the aggregation query.