Eloquent has a lesser known function called withCount():, it helps to get the number of related records inside the main object. It also works with two layers inside, in a hasManyThrough relationship.

In our mini-project example, we will have three models: Users, Posts and Comments. All relationships can be described, see the 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 the table from below figure – the list of users and the number 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();

Then we receive $user->approved_comments_count that we can use in Blade as shown in figure below.

Credit to: https://bit.ly/3HwbhBt

Leave a Reply

Your email address will not be published. Required fields are marked *