Eloquent - where not equal to

Question

I'm currently using the latest Laravel version.

I've tried the following queries:

Code::where('to_be_used_by_user_id', '<>' , 2)->get()
Code::whereNotIn('to_be_used_by_user_id', [2])->get()
Code::where('to_be_used_by_user_id', 'NOT IN', 2)->get()

Ideally, it should return all records except user_id = 2, but it returns blank array. How do I tackle this?

Code::all()

This returns all 4 records.

Code model:

<?php namespace App;

use Illuminate\Database\Eloquent\Model;

class Code extends Model
{

    protected $fillable = ['value', 'registration_id', 'generated_for_user_id', 'to_be_used_by_user_id', 'code_type_id', 'is_used'];

    public function code_type()
    {
        return $this->belongsTo('App\CodeType');
    }

}

Answer 1

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

Answer 2

For where field not empty this worked for me:

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

Answer 3

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"

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

Answer 4

Or like this:

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

Answer 5

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

Answer 6

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

Answer 7

A number of the comments on the previous answers to this question complain that null values are not returned by the given answers.

If you want to be able to return all values that are not your condition but include nulls in a single line - specify using the spaceship operator (null-safe-equals):

Code::whereNot('user_id','<=>',2)