35

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.

clone45
  • 8,952
  • 6
  • 35
  • 43
Arcade
  • 736
  • 2
  • 7
  • 17
  • What did you do so far to find out? I mean not that someone suggests something you already did. – hakre Nov 02 '12 at 14:57
  • If been checking the documentation but can't really find anything about it – Arcade Nov 02 '12 at 15:01
  • 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 Nov 02 '12 at 15:03
  • Then you would count the locations, not the users – Arcade Nov 02 '12 at 15:11
  • Hmm. I see. What does `Location::with("users")->count("users")->get();` return? – hakre Nov 02 '12 at 15:23
  • Can you post your mysql db's? But I think it's job of php. – RDK Nov 02 '12 at 15:27
  • 1
    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 Nov 02 '12 at 21:15

3 Answers3

39

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

ben at colourmill
  • 406
  • 4
  • 4
  • 13
    Although late to the party, just like to point out you still get the n+1 problem with this. You need to use `$location>users->count()` in a foreach , basically don't access the method users(), but access the property, or it does a query each time. – David Mar 24 '15 at 14:34
  • @David thank you so much, you helped clear up the confusion as I was still seeing an n+1 problem. It would help to update this answer to be more accurate. – Paul Redmond Dec 17 '15 at 04:53
13

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)

Bogdan
  • 1,840
  • 1
  • 25
  • 39
8

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

iTech
  • 18,192
  • 4
  • 57
  • 80
  • 6
    Would this use as many DB queries as results from Location::get()? – A_funs Oct 30 '13 at 18:07
  • 36
    This is bad. It results in a [n+1 query issue](http://stackoverflow.com/questions/97197/what-is-the-n1-selects-issue?lq=1) – edi9999 Mar 13 '14 at 11:22
  • In this case, is better using Collection. Because thus you use the PHP for relationships count. `$location->users->count()` – Wallace Vizerra Mar 24 '15 at 11:36