[Request] Get relationship count without loading all objects

[dwightwatson]

I think it would be handy to be able to retrieve the number of objects in a relationship without having to load all the relations from the database as well. If a country has thousands of users it doesn't make much sense loading them in if they aren't being used.

$countries = Country::with('users')->get();

foreach ($countries as $country)
{
     echo $country->name . ' has ' . $country->users->count() . ' users.';
}

Perhaps this syntax could do the trick (assuming most people won't have a counts column in their database).

$countries = Country::withCounts('users')->get();

foreach ($countries as $country)
{
    echo $country->name . ' has ' . $country->counts->users . ' users.';
}

[franzliedke]

I agree that would be cool. For normalized database tables, it could run a grouped SELECT COUNT, for non-normalized queries you might already have a <relation_name>_count column in the table.

[anlutro]

I think the most flexible way to implement this would be to in general allow for eager loading of aggregates. I've been using my own traits and scope methods to achieve this to some success, but it's definitely a tough problem because the query builder doesn't allow for easy adding of subqueries.

[taylorotwell]

This isn't going to happen anytime soon. You can just use $country->users()->count() to get the count without loading the user models.

[maknz]

Would that method not cause an n+1 problem when you have a collection of countries, @taylorotwell ? I've come across the need for eager loading aggregates like this quite a lot. I think the best interim solution is to eager load all the data in and use a regular old php count() on the collection, but having relationship aggregates would certainly be more efficient.

[safecat]

Really need this feature, I am building an online shop, and most of my products have thousands of comments, so If I use:
Product::take(100)->with('comments')->get();
It will take like 100,000 comments from mysql, but I just need comment count for those product.
If I use:
Product::take(100)->get()->each(function($item){
$item->comment_count;
});
There will be 100 queries for getting comment count.

[taylorotwell]

Just run a separate query only to do the count. You don't have to do it through Eloquent.

[safecat]

I decided to add a comment_count column to do this, every time when add a comment, I'll update this column, looks like a good practice ^_^

[Garbee]

@safecat Looks more like bad practice. Why add a column and maintain the code to keep it updated when you can get the same result with a simple raw query? Adding app logic complexity for no good reason is not good practice.

[safecat]

@Garbee “take(100)” is just an example, I may need to get products everywhere, and with many conditions like featured/latest/cheap. And maybe some day I need to get products order by comment_count, this column might be very useful in the future.
With eloquent event, it's very easy to maintain this column, so I think it worse.

[dwightwatson]

@safecat's approach is very similar to how it is done on Rails, so at least the practice isn't unheard of @Garbee. I think an Eloquent event to maintain the column count is just fine.

[franzliedke]

Yep, sometimes it makes sense to lose on the normalization side, to benefit performance.

[AngryUbuntuNerd]

+1

Currently the choice is between n+1 queries (slow) or eager loading with potentially huge PHP objects (slow). Both solutions are not scaling very well.

[Yellow-Ball]

This one really needs addressing, because say If I have a post with 4000 comments, and each comment has x amount of likes, I'd either have the n+1 problem as mentioned above, or try eager loading huge php objects, which again, isn't very scalable.

Taylor, is there no way to solve this?
All I'm looking for, is a way to do the below.

    public function likes()
    {
        return $this->hasMany('Ideadrop\Models\Like')->count();
    }

[jedgueruela]

aside from eloquent event, say i have a reply_count column on my table, is it also ideal to update that through database transactions ?