'Eloquent - where not equal to

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');
    }

}
laraveleloquent


Solution 1:[1]

For where field not empty this worked for me:

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

Solution 2:[2]

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"

enter image description here enter image description here

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

Solution 3:[3]

Or like this:

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

Solution 4:[4]

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

Solution 5:[5]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Abduhafiz
Solution 2
Solution 3 Mladen Janjetovic
Solution 4 Deepesh Thapa
Solution 5 İlter Kağan Öcal

Related Questions