How to (Efficiently) Count Records with Laravel

Counting on a collection¶

The easiest way to get the row count from your query is using the count method. Here's what it looks like:

$users = User::get();

dd($users->count); // 5 (or however many users you have)

This seems pretty straightforward, but what's going on here?

When we run User::get(), this actually returns a Laravel Collection class. At this point, the query has already been run. Laravel has taken the data from your users table and populated a Collection with a bunch of User models.

This means that by using count here, you're actually counting on the collection and not at the database level.

This method is absolutely fine if you don't have too many rows or if you need the data anyway, so feel free to use it. If you just want to count the rows and don't need the data back, though, there's a much faster way.

Using count() directly¶

You can use count directly on the query builder (and not on the collection) to fetch a count from the database. Let's take a look:

dd(User::count()); // 5 (or however many users you have)

Notice we're not using get to fetch the Collection first. This performs a query at the database level like this:

select count(*) as aggregate from `users`

This is much more efficient since we're not fetching the data first and then counting on an array of data within a Collection.

You're also welcome to add any additional clauses to your query before you fetch the count too:

User::whereNotNull('email_verified_at')->count(); // 2

Here's what that query would look like at the database level:

select count(*) as aggregate from `users` where `email_verified_at` is not null

Hopefully, the difference between using get()->count() and just count() is clear now.

Using an aggregate function¶

What if you need to fetch the count of a relationship? For example, imagine we're building a forum and want to count how many posts (replies) exist within discussions.

Here's how we could do this:

$discussions = Discussion::get();

And somewhere in Blade (or wherever you're rendering the discussions):

@foreach ($discussions as $discussion)
    <div>
        <div>{{ $discussion->title }}</div>
        <div>{{ $discussion->posts->count() }} replies</div>
    </div>
@endforeach

Assuming we have a posts relationship, we're using $discussion->posts->count() to display the number of replies. Feels natural, right?

What's happening here, however, is potentially high memory usage from counting on the posts relationship. Behind the scenes, we're fetching all posts (eek) and then counting on a Collection. This is a massive waste because we don't need all the post data within the discussions list.

Using withCount¶

There's a solution, though: the withCount method on our builder. Here's what it looks like:

$discussions = Discussion::withCount('posts')->get();

This adds a COUNT on the posts relationship at the database level and adds a property to each of the Discussion models we're iterating through, postfixed with _count.

Let's refactor the example above:

@foreach ($discussions as $discussion)
    <div>
        <div>{{ $discussion->title }}</div>
        <div>{{ $discussion->posts_count }} replies</div>
    </div>
@endforeach

Now, instead of loading all posts into a Collection just to count them, we're simply accessing a property with the count of the posts.

If you use a tool like laravel-debugbar and refactor this style of counting to use an aggregate, you'll likely notice the memory usage of your application decrease.

There's a load more you can do with aggregates in Laravel, but refactoring your code to use withCount for situations like this can lower the memory usage of your application — try and keep it in mind!