PHP
Efficient Relationship Aggregates with Eloquent `withCount`
Learn to efficiently retrieve counts of related models for a collection of parent models using Eloquent's `withCount` method, avoiding N+1 query problems.
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;
class Post extends Model
{
public function comments(): HasMany
{
return $this->hasMany(Comment::class);
}
}
class Comment extends Model
{
// ...
}
// Usage:
// Get posts with their comments count
// $posts = Post::withCount('comments')->get();
// foreach ($posts as $post) {
// echo $post->title . ' has ' . $post->comments_count . ' comments.';
// }
// Get posts with filtered comments count
// $posts = Post::withCount(['comments' => function ($query) {
// $query->where('approved', true);
// }])->get();
// foreach ($posts as $post) {
// echo $post->title . ' has ' . $post->comments_count . ' approved comments.';
// }
// Multiple aggregates: withCount, withSum, withAvg, withMax, withMin
// Assuming 'likes' relationship and 'amount' column on it
/*
$products = Product::withCount('reviews')
->withSum('orders', 'amount')
->withAvg('ratings', 'score')
->get();
foreach ($products as $product) {
echo $product->name . ' has ' . $product->reviews_count . ' reviews, ' .
'total orders of ' . $product->orders_sum_amount . ', and ' .
'average rating of ' . $product->ratings_avg_score . '.';
}
*/
How it works: Eloquent's `withCount()` method (and its siblings like `withSum()`, `withAvg()`, `withMax()`, `withMin()`) allows you to count related models without loading all of them, which can prevent N+1 query issues. When you query for posts using `withCount('comments')`, each post model will automatically have a `comments_count` attribute attached, containing the number of associated comments. You can also pass a closure to `withCount` to apply conditions to the count, giving you fine-grained control over the aggregate calculation.