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

}

This question is related to laravel eloquent

The answer is


For where field not empty this worked for me:

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

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

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