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.

score 34 · Accepted Answer · answered Apr 28 '18 at 22:35

34

Use this:

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

answered Apr 28 '18 at 22:35

Jonas Staudenmeir

24,815
6
63
109

Actually this works now, didn't work before because my relation wasn't setup correctly. – user3718908x100 Apr 29 '18 at 13:02
1

This only works if the query was grouped by, otherwise it throws `unknown column cars_count`. – Ken Sep 09 '20 at 14:10
And how do you group by the query? This answer is incomplete, it doesn't work as is – Tofandel May 25 '21 at 21:35
'cars_count' alias is already created by the 'withCount' function @Ken – Mariano Argañaraz Dec 27 '22 at 04:15

score 5 · Answer 2 · edited Jul 20 '21 at 00:06

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 '21 at 00:06

Mahmoud Abdelsattar

1,299
1
15
31

answered Apr 29 '18 at 00:13

Dorin Musteața

162
1
12

1

This will work, but Jonas' solution is strictly better since this one executes 1 more subquery. – DevK Apr 29 '18 at 00:24
1

Jonas' soution will say that there is no column 'cars_count' – Dorin Musteața Apr 29 '18 at 00:36
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. – DevK Apr 29 '18 at 00:40
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/ – user3718908x100 Apr 29 '18 at 13:04
1

@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. – Dorin Musteața Apr 29 '18 at 13:07
@DevK what do you mean by if the relation is 'set up correctly'? Mine is a simple `belongsToMany` and I am getting the 'undefined column` error. Query looks like `Group::withCount('users')->having('users_count', '<', '2')->get();` – Stetzon Jul 17 '19 at 18:46
Note that both withCount() and has() insert the same subquery into the sql and so it is run twice. `withCount()` adds as an alias select: `select (select count(*) ...) as cars_count`. And `has()` adds as where: `where (select count(*) ...) < N`. I would only use `has()` to prevent double sql evaluation. – Ken Sep 09 '20 at 13:31

score 2 · Answer 3 · edited Feb 11 '22 at 17:40

2

This is the best way:

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

edited Feb 11 '22 at 17:40

answered Feb 08 '22 at 10:57

Muhammed Musabeh

56
2

3

Hi and welcome! Please explain why this is the best solution in your opinion. – Feb 08 '22 at 11:15
Because its simple , one line code and you don`t have to load any relation or using "having" query. – Muhammed Musabeh Feb 09 '22 at 11:16

score 0 · Answer 4 · edited Jul 20 '21 at 00:40

0

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();

edited Jul 20 '21 at 00:40

Mahmoud Abdelsattar

1,299
1
15
31

answered Apr 28 '18 at 22:24

Dorin Musteața

162
1
12

No I haven't, I forgot to add that Customer's and Cars are joined on a pivot table. – user3718908x100 Apr 28 '18 at 22:34
Please , provide a **sqlfiddle**. – Dorin Musteața Apr 28 '18 at 22:37
It's just a belongsToMany relation. Meaning I have a customer_car pivot table in my database. – user3718908x100 Apr 28 '18 at 22:40
I know. But i need to write that query in raw sql , so i can write that query in laravel. – Dorin Musteața Apr 28 '18 at 22:45
Please add some 3-4 rows for each table. – Dorin Musteața Apr 28 '18 at 22:53

Laravel Where Count > N

4 Answers4

Linked