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
Eloquent has a method for that (Laravel 4.*/5.*);
Model::whereNotNull('sent_at')
Laravel 3:
Model::where_not_null('sent_at')
whereNull('deleted_at') and I got my query running.
whereNotNull() method (and several others that were undocumented previously) were added to the documentation in version 5.1: laravel.com/docs/5.1/queries#where-clauses .
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', '<>', '');
}
'and' stands for?
public function where($column, $operator = null, $value = null, $boolean = 'and'); and location - ".....\vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php"We can use
Model::whereNotNull('sent_at');
Or
Model::whereRaw('sent_at is not null');
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.
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
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.
where deleted_at IS NULL at the end of each query. So your query and the SoftDeletes query will always lead to an empty results. Use the onlyTrashed() method instead.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.
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");
You can do it by simply following:
Model::whereNotNull('sent_at')->get();
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:
$deliveryTo = DeliveryTo::where([
['delete_flg', '=', 0],
['area_id', '=', 1],
['area_id', '!=', NULL],
])->get();
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();
Db::table to find the soft deleted records. You could filter them with the method withTrashed(), like is told in the docs: laravel.com/docs/5.3/eloquent
!=instead of IS NOT.