Laravel Where Not Equal Not Working As Expected

Question

I've done some searcing and can't find the answer to this, so some help would be much appreciated.

I'm running the following eloquent statement:

        $collection= Table1::select('table1.*','table2.finish_status_id')
                                ->join('table2','table2.id','=','table1.table2_id')
                                ->where('table1.id',$id)
                                 ->where('finish_status_id','=',6)
                                ->paginate(25);

This query works.

However, what I actually want is this: ->where('finish_status_id','!=',6) This does not work and I get no results.

These following query variations all work as expected

 ->where('finish_status_id','=',6)
 ->where('finish_status_id','=',null)
 ->where('finish_status_id','!=',null)
 ->where('finish_status_id','<>',null)

If there a reason I haven't seen as to why != is not working?

Update: As per ThataL suggestion, I tried writing my query in SQL to get it to work. I was unable to get what I originally planned, and after some digging found thi thread: SQL is null and = null

Where the answer indicate NULL does not equal 'null' in SQL. Therefore it is treated as unknown, rather than a value filled with the value of 'null'.

With my != 6 phrase, I was expecting to receive all of the null values as well. However, on further inspection, that query works, except it also does not return null records.

After further searching I found what I was meant to be looking for on this thread: Laravel 4 Eloquent Query Using WHERE with OR AND OR?

Originally, simply adding a 'whereNull('finish_Status_id')' did not work, because being an or, it essentially ignore my previous where clause. (i.e., it got everything that was null, or, everything with $id).

I then updated my code to the following

$collection= Table1::select('table1.*','table2.finish_status_id')
                                ->join('table2','table2.id','=','table1.table2_id')
                                ->where('table1.id',$id)
                                ->where(function ($query) {
                                        $query->where('finish_status_id', '!=', 6)
                                              ->orWhereNull('finish_status_id');
                                    })
                                ->paginate(25);

This provide me with the solution I was after.

Can you share the sample raw query that you wanted to implement in laravel eloquent. — ThataL, Oct 22 '21 at 11:48
https://stackoverflow.com/questions/28256933/eloquent-where-not-equal-to — maki000, Oct 22 '21 at 11:49
You should add a table name with column name `->where('table2.finish_status_id','!=',6)` — Amit Senjaliya, Oct 22 '21 at 11:49

score 0 · Answer 1 · answered Oct 23 '21 at 10:25

This is the correct code to include null into the query.

$collection= Table1::select('table1.*','table2.finish_status_id')
                                ->join('table2','table2.id','=','table1.table2_id')
                                ->where('table1.id',$id)
                                ->where(function ($query) {
                                        $query->where('finish_status_id', '!=', 6)
                                              ->orWhereNull('finish_status_id');
                                    })
                                ->paginate(25);

Laravel Where Not Equal Not Working As Expected

1 Answers1