Laravel eloquent counting a relation

30
votes

I'm new to laravel and eloquent and I'm not sure if this is even possible. but I have 2 tables with a one to many relationship. One is "locations" and one is "users". One location can have many users.

So if I wanted to get all locations with all users I would just do this:

Location::with("users")->get();

But I also want to know how many users each location has, I tried doing this

Location::with("users")->count("users")->get();

But that didn't work.

3
phplaraveleloquent
What did you do so far to find out? I mean not that someone suggests something you already did. - hakre
If been checking the documentation but can't really find anything about it - Arcade
If Location::with("users")->get(); then Location::with("users")->count(); might work, too. Have you tried that (I've just scanned the docs, never used that library)?. - hakre
Then you would count the locations, not the users - Arcade
If you already loaded a relationship you can count it like count($location->relationships['users']) when you are looping trough the locations for example with a foreach ($locations as $location) Or you want to count all the users who has a location not for each? - TLGreg

3 Answers

37
votes

The n+1 issue that was mentioned doesn't occur if you use eager loading.

$locations = Location::with('users')->get();

$locations->users()->count();

This should result in three queries, no matter how many users or locations you have.

  • count query against the location model
  • select * from locations
  • select * from users where in

The confusion arises from the fact that this also works:

$locations = Location::all();

$locations->users()->count();

But in this case, it does query once for each location.

See the docs for more info: http://laravel.com/docs/eloquent#eager-loading

12
votes

You should be using just withCount but I guess it wasn't available back in 2012.

So here's how it should look like:

Location::with("users")->withCount("users")->get();

And you will have an users_count attribute available on you object.

Read the docs for more details: https://laravel.com/docs/5.5/eloquent-relationships#querying-relations (scroll down a bit and you'll see Counting Related Models)

8
votes

You need to call the count method on each Location record to get users count per location, here is an example:

foreach(Location::get() as $location) // or foreach(Location::with("users")->get() as $location)
{
  echo $location->users()->count();
}

This should solve your problem and give you the number of users per each location. You can add a check in the loop to ignore locations with users' count = 0