Eloquent - where not equal to

Use where with a != operator in combination with whereNull

Code::where('to_be_used_by_user_id', '!=' , 2)->orWhereNull('to_be_used_by_user_id')->get()

---

For where field not empty this worked for me:

->where('table_name.field_name', '<>', '')

---

While this seems to work

Code::query()
    ->where('to_be_used_by_user_id', '!=' , 2)
    ->orWhereNull('to_be_used_by_user_id')
    ->get();

you should not use it for big tables, because as a general rule "or" in your where clause is stopping query to use index. You are going from "Key lookup" to "full table scan"

https://i.stack.imgur.com/uPY2C.png

Instead, try Union

$first = Code::whereNull('to_be_used_by_user_id');

$code = Code::where('to_be_used_by_user_id', '!=' , 2)
        ->union($first)
        ->get();

---

Or like this:

Code::whereNotIn('to_be_used_by_user_id', [2])->get();

---

Fetching data with either null and value on where conditions are very tricky. Even if you are using straight Where and OrWhereNotNull condition then for every rows you will fetch both items ignoring other where conditions if applied. For example if you have more where conditions it will mask out those and still return with either null or value items because you used orWhere condition

The best way so far I found is as follows. This works as where (whereIn Or WhereNotNull)

Code::where(function ($query) {
            $query->where('to_be_used_by_user_id', '!=' , 2)->orWhereNull('to_be_used_by_user_id');                  
        })->get();

---

Here's a useful scope method that you can add to your model and use it for different columns.

/**
 * @param Builder $query
 * @param string $field
 * @param $value
 * @return Builder
 */
public function scopeWhereNot(Builder $query, string $field, $value): Builder
{
    return $value === null
        ? $query->whereNotNull($field)
        : $query->where(function ($q) use ($field, $value) {
            return $q->where($field, '<>', $value)
                ->orWhereNull($field);
        });
}

and use it as follows

Code::whereNot('to_be_used_by_user_id', 2)->get()

---