0

I got the following:

  • User, Role, with a role_user pivot table and a belongsToMany relationship

  • User, Location, with a location_user pivot table and a belongsToMany relationship

There's 2 roles for the user: owner & worker

Location has a 'workers_max' field

I try to build a query to get All locations where 'workers_max' is greater than number of associated workers.

I tried: 

Locations::withCount('workers')->where('workers_max', '>', 'workers_count')->get();

but it returns all the locations.

sebap
  • 1,601
  • 5
  • 24
  • 41

2 Answers2

1

Try to look at the query using Laravel Query Logging, you will notice the withCount() is using where to query the result. If you want to filter on aggregates, you need to use having.

Just a little change to your query, you should be able to get what you want.

See how to use havingRaw

Difference between where and having

HG Sim
  • 126
  • 4
0

Update

It seems the result of withCount() cannot be used in a where clause because it's an aggregate function. I think you will have to use Join.

If you don't want to join, you can do something VERY BAD. Load all of them, workers_count will be available for you, then filter the collection like this

$locations = Locations::withCount('workers')->get();
$goodLocations = $locations->filter(function($location, $key){
    return $location->workers_max > $location->workers_count;
});

As I said, not the best solution, but I think this can get the job done, as long as the collection is not huge.

For efficiency, you will have to use Join

EddyTheDove
  • 12,979
  • 2
  • 37
  • 45
  • Thanks, i did simplified a bit since i have shortcuts: Location::withCount('workers')->where('users_max', '>', 2)->get() is working but Location::withCount('workers')->where('users_max', '>', 'workers_count') is NOT :( – sebap Apr 30 '17 at 14:08
  • Oh ok. Good. Sorry I couldn't give you the exact query you wanted. Did not know about your shortcuts. BTW, is your field 'users_max' or 'worker_max'. Bit of confusion here. – EddyTheDove Apr 30 '17 at 14:09
  • No what i mean is that withCount is good starting point but the comparison with workers_count is not working : it returns all the locations.. – sebap Apr 30 '17 at 14:11
  • Oh sorry. I see now. Let me have another look – EddyTheDove Apr 30 '17 at 14:12
  • Thank you very much, it works and i already learned a lot. But as you say i would prefer to use join, to perform a single scopeQuery. Just don't understand how to do so ! – sebap Apr 30 '17 at 14:37