CODEWITHSUNDEEP
phplaraveleloquent
Sagar Gautam
Sagar Gautam

Reputation: 9389

Laravel Where Clause not working for Null values

I have a customers table and Customer as Model. I am trying to get all the customers except customer category with id 1. The customer_category_id can be nullable.

I have following query.

Customer::where('customer_category_id', '!=', 1)->with('customerMeta')->get();

Above query doesn't give the customers with null customer category id. What would be the reason ?

I need all the customers with category null or category not equals to 1

Any kind of suggestions are appreciated.

Upvotes: 0

Views: 3283

Answers (6)

Andrew
Andrew

Reputation: 1

this is not a problem of Laravel but of MySQL When you deal with null values it's better to use NULL-safe equal to operator <=> ( https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_equal-to )

Customer::whereNot('customer_category_id', '<=>', 1)->with('customerMeta')->get();

Upvotes: 0

mare96
mare96

Reputation: 3869

This should work:

Customer::where(function ($query) {
            $query->where('customer_category_id', '!=', 1)
                  ->orWhereNull('customer_category_id')
        })
        ->with('customerMeta')
        ->get();

Good luck!

Upvotes: 8

Wail Hayaly
Wail Hayaly

Reputation: 1227

If you want to return the null category as well, you can use whereNull

Customer::where('customer_category_id', '!=', 1)
->whereNull('customer_category_id')
->with('customerMeta')
->get();

or you can use multiple where clauses like this:

$query->where([
    ['customer_category_id', '!=', '1'],
    ['customer_category_id', '=', null],
    ...
])

Upvotes: 0

Rouhollah Mazarei
Rouhollah Mazarei

Reputation: 4153

Change your query like this:

Customer::where('customer_category_id', '!=', '1')->with('customerMeta')->get();

Because the 1 also means true, so you have to use a string .

Upvotes: 0

PaulusDixit
PaulusDixit

Reputation: 26

That query should work.

You could try and run it in artisan tinker. Before you run the query activate query logging:

DB::connection()->enableQueryLog();

Then run the query.

Afterwards, run:

DB::getQueryLog();

You'll now see the query that was created. Copy this query into a SQL Client and tweak it.

Hope this helps.

Upvotes: 0

PtrTon
PtrTon

Reputation: 3855

Because null is also not equal to 1. You should probably add ->whereNotNull('customer_category_id') to your query:

Customer::where('customer_category_id', '!=', 1)
->whereNotNull('customer_category_id')
->with('customerMeta')
->get();

Upvotes: 2

Related Questions