Eloquent withCount(): Get Related Records Amount

Eloquent has one less-known function called withCount(): it helps to get the amount of related records inside of the main object. It also works with two layers deep, inside of hasManyThrough relations. Let’s take a look.

In our example mini-project, we will have three models: User, Post and Comment. All relationships can be described, looking at app/User.php model:

public function posts()
{
    return $this->hasMany(Post::class);
}

public function comments()
{
    return $this->hasManyThrough(Comment::class, Post::class);
}

Now, we will try to show this table – the list of users and the amount of their posts/comments:

To do that, here’s our UserController code:

public function index()
{
    $users = User::withCount(['posts', 'comments'])->get();
    return view('users', compact('users'));
}

Every parameter that is specified in withCount() method, becomes main object’s _count property. So in this case, we will have $user->posts_count and $user->comments_count variables.

Then, in our Blade file we have this:

<table class="table">
    <thead>
        <tr>
            <th>User</th>
            <th class="text-center">Posts</th>
            <th class="text-center">Comments</th>
        </tr>
    </thead>
    <tbody>
        @foreach ($users as $user)
            <tr>
                <td>{{ $user->name }}</td>
                <td class="text-center">{{ $user->posts_count }}</td>
                <td class="text-center">{{ $user->comments_count }}</td>
            </tr>
        @endforeach
    </tbody>
</table>

Notice, that withCount() works with both hasMany() relationship, and also 2nd level deep with hasManyThrough().

Another example is that we can even filter the query with withCount() relationship. Let’s say that our comments table has a column approved, and then we can filter that separately and even assign an alias to that column name:

$users = User::withCount([
    'posts', 
    'comments', 
    'comments as approved_comments_count' => function ($query) {
        $query->where('approved', 1);
    }])
    ->get();

And then we receive $user->approved_comments_count that we can use in Blade.

More examples of withCount() method – in the official Laravel documentation.

Like our articles?
Check out our Laravel online courses!

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here