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

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
laraveleloquent


Solution 1:[1]

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

Model::whereNotNull('sent_at')

Laravel 3:

Model::where_not_null('sent_at')

Solution 2:[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', '<>', '');
}

Solution 3:[3]

We can use

Model::whereNotNull('sent_at');

Or

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

Solution 4:[4]

If you wanted to use the DB facade:

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

Solution 5:[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.

Solution 6:[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.

Solution 7:[7]

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

Solution 8:[8]

You can do it by simply following:

Model::whereNotNull('sent_at')->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 ishegg
Solution 2 Atiqur
Solution 3 Md. Saidur Rahman Milon
Solution 4 Jon
Solution 5 Rockin4Life33
Solution 6 JON
Solution 7
Solution 8 tayeb320

Related Questions