Laravel Where Clause not working for Null values

Question

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.

See [here](https://stackoverflow.com/questions/9608639/mysql-comparison-with-null-value) for NULL comparison. — nice_dev, Aug 13 '19 at 07:35

mare96 · Accepted Answer · 2022-11-18T12:14:27.267

8

This should work:

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

Good luck!

edited Nov 18 '22 at 12:14

answered Aug 13 '19 at 07:41

mare96

3,749
1
16
28

1

If you need to fetch data that has either a valid value or null value, I guess this is the right way to start. Because all other solution using orWhereNull and stuffs are tricky and traps. – Deepesh Thapa Sep 03 '20 at 12:52

score 2 · Answer 2 · answered Aug 13 '19 at 07:32

2

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

answered Aug 13 '19 at 07:32

PtrTon

3,705
2
14
24

I have tried, but I need data with null category as well as with category not equals to 1 – Sagar Gautam Aug 13 '19 at 07:32
1

Sorry I didn't read your question well enough. In that case your query should work unless the relation for `->with('customerMeta')` is based off of the `customer_category_id` column – PtrTon Aug 13 '19 at 07:34
Customer meta is related based on customer id not customer category id – Sagar Gautam Aug 13 '19 at 07:36

score 0 · Answer 3 · answered Aug 13 '19 at 07:36

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.

Rouhollah Mazarei · Answer 4 · 2019-08-13T07:39:03.623

0

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 .

edited Aug 13 '19 at 07:39

answered Aug 13 '19 at 07:37

Rouhollah Mazarei

3,969
1
14
20

1

Bro, what's the difference from the query i have added ? – Sagar Gautam Aug 13 '19 at 07:38
Please explain what you've changed and why, such that others can learn from it – Nico Haase Aug 13 '19 at 07:39
Just try it. At most it will not work. But I think it will. – Rouhollah Mazarei Aug 13 '19 at 07:40

score 0 · Answer 5 · answered Aug 13 '19 at 07:40

0

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],
    ...
])

answered Aug 13 '19 at 07:40

Wail Hayaly

1,057
1
15
31

You can't compare `null` with a `=`. You need to use `is` or `is not` depending on the context. – nice_dev Aug 13 '19 at 07:43
This will check for null category and not equals to 1 which will return empty result. I need null category or not equals – Sagar Gautam Aug 13 '19 at 07:43

Laravel Where Clause not working for Null values

5 Answers5