Eloquent won't return row where('foo', '!=', 'bar') if foo is NULL

Question

I have this query in Laravel 5.7:

users = User::where('role', 'sales')
            ->where('tasks', '!=', 'lead')
            ->orderBy('last_name')
            ->get();

I expected this query to get all users that have the sales role, unless they are marked as lead in the tasks column (which may be NULL). But now I had to realize that in my app, whenever tasks is NULL, the user won't be part of the collection.

Is this usual behaviour? If so, how should I approach this problem? If not, what might cause it?

//... ->where(function($q) { $q->where('tasks', '!=', 'lead') ->orWhereNull('tasks'); }); //... — Foued MOUSSI, Jan 15 '20 at 16:22

score 3 · Accepted Answer · answered Jan 15 '20 at 16:20

null in tricky in SQL: nothing is equal to null, and nothing is different than null (even null itself). So basically, condition null != 'foo' is false.

You need to explictly chek for nullness. In pure SQL, that would look like:

where tasks is null or tasks != lead

In Lavarel, this can be done with an advanced where clause:

->where(function ($query) { $query->where('tasks', '!=', 'lead')->orWhereNull('task'); } )

Eloquent won't return row where('foo', '!=', 'bar') if foo is NULL

1 Answers1