How do you check "if not null" with Eloquent?

Question

How do you check if a field is not null with Eloquent?

I tried Model::where('sent_at', 'IS NOT', DB::raw('null'))->... but it gives IS NOT as a binding instead of a comparison.

This is what DB::getQueryLog() says about it:

  'query' => string 'select * from my_table where sent_at = ? and profile_id in (?, ?) order by created_at desc' (length=101)
  'bindings' => 
    array (size=3)
      0 => string 'IS NOT' (length=6)
      1 => int 1
      2 => int 4

Answer 1

Eloquent has a method for that (Laravel 4.*/5.*);

Model::whereNotNull('sent_at')

Laravel 3:

Model::where_not_null('sent_at')

Answer 2

If someone like me want to do it with query builder in Laravel 5.2.23 it can be done like ->

 $searchResultQuery = Users::query(); 
 $searchResultQuery->where('status_message', '<>', '', 'and'); // is not null
 $searchResultQuery->where('is_deleted', 'IS NULL', null, 'and'); // is null 

Or with scope in model :

public function scopeNotNullOnly($query){

    return $query->where('status_message', '<>', '');
}

Answer 3

We can use

Model::whereNotNull('sent_at');

Or

Model::whereRaw('sent_at is not null');

Answer 4

If you wanted to use the DB facade:

DB::table('table_name')->whereNotNull('sent_at')->get();

Answer 5

I see this question is a bit old but I ran across it looking for an answer. Although I did not have success with the answers here I think this might be because I'm on PHP 7.2 and Laravel 5.7. or possible because I was just playing around with some data on the CLI using Laravel Tinker.

I have some things I tried that worked for me and others that did not that I hope will help others out.

---

I did not have success running:

    MyModel::whereNotNull('deleted_by')->get()->all();             // []
    MyModel::where('deleted_by', '<>', null)->get()->all();        // []
    MyModel::where('deleted_by', '!=', null)->get()->all();        // []
    MyModel::where('deleted_by', '<>', '', 'and')->get()->all();   // []
    MyModel::where('deleted_by', '<>', null, 'and')->get()->all(); // []
    MyModel::where('deleted_by', 'IS NOT', null)->get()->all();    // []

All of the above returned an empty array for me

---

I did however have success running:

    DB::table('my_models')->whereNotNull('deleted_by')->get()->all(); // [ ... ]

This returned all the results in an array as I expected. Note: you can drop the all() and get back a Illuminate\Database\Eloquent\Collection instead of an array if you prefer.

Answer 6

in laravel 5.4 this code Model::whereNotNull('column') was not working you need to add get() like this one Model::whereNotNull('column')->get(); this one works fine for me.

Answer 7

Checking "not null"

Model::whereNotNull('column1');

Checking "null"

Model::whereNull('column1');

Another solution is using whereRaw()

Model::whereRaw("column1 IS NOT NULL");
Model::whereRaw("column1 IS NULL");

Answer 8

You can do it by simply following:

Model::whereNotNull('sent_at')->get();

Answer 9

You can also use raw query.

Remember: Reports is my model and I am using where raw, the best thing of the raw query is that you can use multiple types of operators such as AND, OR etc just by passing as a string.

For example: WHERE condition1 AND condition2 AND condition3 ...;

---

Reports::whereRaw("column1 IS NOT NULL AND column2 IS NOT NULL");

The above query will be executed as:

Select * from reports where column IS NOT NULL AND column2 IS NOT NULL.

For more understanding of IS Null and IS not null operators:

https://www.w3schools.com/sql/sql_and_or.asp

Answer 10

$deliveryTo = DeliveryTo::where([
            ['delete_flg', '=', 0],
            ['area_id', '=', 1],
            ['area_id', '!=', NULL],
        ])->get();

Answer 11

If you want to search deleted record (Soft Deleted Record), do not use Eloquent Model Query.

Use the Db::table query instead. E.g.:

Instead of:

$stu = Student::where('rollNum', '=', $rollNum . '-' . $nursery)->first();

Use:

$stu = DB::table('students')->where('rollNum', '=', $newRollNo)->first();