Laravel withCount() with Where Condition: Counting Related Models with Filtering

Published on : June 11,2023

In Laravel, the withCount() method allows you to count the number of related models while applying a where condition. This feature is useful for scenarios where you need to retrieve the count of specific related models based on a certain condition. In this tutorial, we will explore how to use the withCount() method in Laravel with a where condition. By the end of this guide, you'll be able to perform filtered counts on related models in your Laravel applications.

Step 1: Define Eloquent Relationships

First, ensure that you have established the appropriate Eloquent relationships between your models. For example, let's consider a scenario where you have a "Post" model that has many "Comments" associated with it. Define the relationship in your Post model:

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
}

Step 2: Perform Filtered Count with withCount()

To perform a filtered count on related models, you can use the withCount() method in combination with a where condition. For example, let's count the number of comments that have a status of "approved" for each post. In your controller or query builder, use the following code:

use App\Models\Post;

$postsWithApprovedCommentCount = Post::withCount(['comments as approved_comment_count' => function ($query) {
    $query->where('status', 'approved');
}])->get();

In this example, we use the withCount() method and pass an array where the key represents the relationship name ('comments as approved_comment_count') and the value is a closure that defines the where condition.

Step 3: Access the Counted Data

The $postsWithApprovedCommentCount variable now holds a collection of Post models, each with an additional 'approved_comment_count' attribute representing the count of approved comments for that post.

To access the counted data, iterate over the collection and retrieve the 'approved_comment_count' attribute:

foreach ($postsWithApprovedCommentCount as $post) {
    $approvedCommentCount = $post->approved_comment_count;
    // Do something with the count
}

You can then perform any desired actions with the counted data, such as displaying it in your views or making decisions based on the count.

The withCount() method in Laravel allows you to perform filtered counts on related models by applying a where condition. By following the steps outlined in this tutorial, you now have the knowledge to leverage the withCount() method to count specific related models while filtering the results.

Experiment with different relationships, where conditions, and attribute names to suit your application's requirements. Laravel's Eloquent provides powerful tools for working with related data and performing data counts efficiently.

Happy counting related models in Laravel!

Categories : Laravel

Tags : Laravel Laravel 10 Eloquent relationships withCount() method filtered counts data counting in Laravel

Abhay Dudhatra

I am a full-stack developer who is passionate about creating innovative solutions that solve real-world problems. With expertise in technologies such as PHP, Laravel, Angular, Vue, Node, Javascript, JQuery, Codeigniter, and Bootstrap, I love to share my knowledge and help others in the industry through writing tutorials and providing tips. Consistency and hard work are my mantras, and I constantly strive to improve my skills and stay up-to-date with the latest advancements in the field. As the owner of Open Code Solution, I am committed to providing high-quality services to my clients and helping them achieve their business objectives.