[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 ?

[maknz]

You could, but then you're relying on the persistence layer to handle it. If you moved to another DBMS, you might forget it and then it'll break.

Keeping a column for counting records is bad, in my opinion. If implemented in the application, any other apps using the database will cause the count to be inconsistent. If implemented in the database, the application is replying on the black magic of the DBMS and just has to trust the count will be updated. I think the best way at the moment is to run raw queries behind the scenes to accomplish it as @taylorotwell suggested in lieu of a baked-in solution (if eager loading and counting the objects will be too inefficient for your use case).

[freezedriedpop]

Just stumbled on this looking for the same thing. What you could do is something like this:

class Comment extends Eloquent {
    public function likeCount()
    {
        //We use "hasOne" instead of "hasMany" because we only want to return one row.
        return $this->hasOne('Like')->select(DB::raw('id, count(*) as count'))->groupBy('id');
    }
}

//Then you can access it like this
$comment = Comment::with('likeCount')->find($id);

echo $comment->likeCount->count;

But that's not very pretty, so you could use some Eloquent magic to do this instead

class Comment extends Eloquent {
    public function likeCountRelation()
    {
        //We use "hasOne" instead of "hasMany" because we only want to return one row.
        return $this->hasOne('Like')->select(DB::raw('id, count(*) as count'))->groupBy('id');
    }

    //This is got via a magic method whenever you call $this->likeCount (built into Eloquent by default)
    public function getLikeCountAttribute()
    {
        return $this->likeCountRelation->count;
    }
}

//Then you can access it like this
$comment = Comment::with('likeCountRelation')->find($id);

echo $comment->likeCount;

[bizsimon]

�Thanks @freezedriedpop
Just a little update in case anyone run into this

    public function getLikeCountAttribute()
    {
        // when there are no records found with the relation, likeCountRelation() would return null, default that to 0
        if ($this->likeCountRelation) return $this->likeCountRelation->count;
        return 0;
    }

[olimorris]

@bizsimon and @freezedriedpop many thanks for this.

[barryvdh]

So this is an alternative approach using a subselect, what do you think?
#13414

[olimorris]

@barryvdh a very, very nice addition. A feature that I am surprised has taken so long to implement in the core.