Laravel Where Count > N

Question

I have 2 models in my app:

1. Customer.php

2. Car.php

Now I would like to run a query that returns all customers that have less than 2 cars. Where 2 is a number that can be changed by the user.

I have tried this but it didn't work, it just returns all customer records:

$customers = Customer::whereHas("cars", function($query) { $query->selectRaw("count(*) < ?", [2]); }) ->get();

Edit: The two models are linked in a pivot table, meaning A customer can have more than 1 car and a Car can belong to more than 1 customer.

Jonas Staudenmeir · Accepted Answer · 2018-04-28 22:35:07Z

39

Use this:

$customers = Customer::withCount('cars') ->having('cars_count', '<', 2) ->get();

answered Apr 28, 2018 at 22:35

Jonas Staudenmeir

26k7 gold badges70 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3718908x100 Over a year ago

Actually this works now, didn't work before because my relation wasn't setup correctly.

Ken Over a year ago

This only works if the query was grouped by, otherwise it throws unknown column cars_count.

Tofandel Over a year ago

And how do you group by the query? This answer is incomplete, it doesn't work as is

Mariano Argañaraz Over a year ago

'cars_count' alias is already created by the 'withCount' function @Ken

Mahmoud Abdelsattar · Accepted Answer · 2021-07-20 00:06:47Z

5

So , here is the result.

Relation in model Customer.php

public function cars() { return $this->belongsToMany('App\Car','car_customer','car_id','customer_id'); }

Query to get all customers with N cars:

 $userInput = 2; $data = Customer::with('cars') ->withCount('cars') ->has('cars', '<', $userInput) ->orderBy('cars_count', 'desc') ->get();

Where the $userInput is your 'N'.

edited Jul 20, 2021 at 0:06

Mahmoud Abdelsattar

1,5471 gold badge20 silver badges36 bronze badges

answered Apr 29, 2018 at 0:13

Dorin Musteața

1601 silver badge12 bronze badges

8 Comments

DevK Over a year ago

This will work, but Jonas' solution is strictly better since this one executes 1 more subquery.

Dorin Musteața Over a year ago

Jonas' soution will say that there is no column 'cars_count'

DevK Over a year ago

Not if the relation is set up correctly. If it isn't this won't work either. Also, why would you expect ->orderBy('cars_count') to work if there's no column cars_count? As for the explanation, ->withCount('cars') adds a cars_count to the select. It makes a subquery that counts the relation.

user3718908x100 Over a year ago

Thanks, this worked, @devk was right, Jonas's answer didn't work for me initially since my relations were not setup correctly, thanks for your help o/

Dorin Musteața Over a year ago

@user3718908 ,whereHas() is the same as has() , but whereHas() allows to specify additional filters on related model , has() filters selecting model based on a relation.

|

Muhammed Musabeh · Accepted Answer · 2022-02-11 17:40:53Z

4

This is the best way:

$customers = Customer::has('cars','<', 2)->get();

edited Feb 11, 2022 at 17:40

user5446912

answered Feb 8, 2022 at 10:57

Muhammed Musabeh

962 bronze badges

2 Comments

user5446912 Over a year ago

Hi and welcome! Please explain why this is the best solution in your opinion.

Muhammed Musabeh Over a year ago

Because its simple , one line code and you don`t have to load any relation or using "having" query.

Mahmoud Abdelsattar · Accepted Answer · 2021-07-20 00:40:53Z

-1

Have you tried this approach?

$input = 2; $customers = Customer::whereHas("cars", function($query) use ($input) { $query->where(DB::raw("count(cars.id)"), "<", DB::raw($input)) })->get();