PHP
Efficiently Counting Related Models with Eloquent `withCount`
Discover how to efficiently count related models using Eloquent's `withCount` method, avoiding N+1 queries for aggregate data and boosting performance.
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Post extends Model
{
public function comments()
{
return $this->hasMany(Comment::class);
}
public function tags()
{
return $this->belongsToMany(Tag::class);
}
}
class Comment extends Model
{
public function post()
{
return $this->belongsTo(Post::class);
}
}
class Tag extends Model
{
public function posts()
{
return $this->belongsToMany(Post::class);
}
}
// 1. Basic usage: Count all comments for each post
$posts = Post::withCount('comments')->get();
foreach ($posts as $post) {
echo "Post: {$post->title}, Comments: {$post->comments_count}
";
}
// 2. Counting multiple relationships
$posts = Post::withCount(['comments', 'tags'])->get();
foreach ($posts as $post) {
echo "Post: {$post->title}, Comments: {$post->comments_count}, Tags: {$post->tags_count}
";
}
// 3. Conditional counting
$posts = Post::withCount(['comments' => function ($query) {
$query->where('approved', true);
}])->get();
foreach ($posts as $post) {
echo "Post: {$post->title}, Approved Comments: {$post->comments_count}
";
}
How it works: `withCount` is an Eloquent method that efficiently counts the number of related models for each model in a query without actually loading the related models themselves. This prevents the N+1 query problem for aggregate counts, significantly improving performance. The count is added as a `_count` attribute (e.g., `comments_count`) to the parent model. You can count multiple relationships and even add conditions to the count query using a closure, allowing for highly specific aggregations.