Eloquent select rows with empty string or null value

user_966187 0
Viewed 58711

I have something like $user->albums()->where('col', NULL), it works fine then I tried to extend it to empty strings with $user->albums()->where('col', NULL)->or_where('col', '') and it's not working.

Also I saw on this post that I could use where_null('col') but it's not working and it's not documented. Any simple method to select where empty or NULL col

eloquent laravel relationship orm php
5 Answers

How about this:

$user->albums()->whereRaw("NOT col > ''")

This way you can check both conditions at the same time

user_112820040

Try this query:

$users = DB::table('users')
        ->whereRaw('col = "" OR col IS NULL')
        ->get();
user_62468180

I always encourage to create queries with the main Laravel functions that are most used. That's why you must have 2 things in mind:

  • algorithm. key1 = value1 AND key2 = value2 OR key3 = value3. Be very carreful about precedence because in the way I exemplified there will be a main OR not an AND with OR inside
  • using where(), whereIn(), whereNull and closure instead of whereRaw(). whereRaw is using more memory than any others I mentioned.

So, to resume your answer:

OR condition

$users = DB::table('users')
        ->where('col', '=', '')
        ->orWhere('col','=','')
        ->whereNull('col')
        ->get();

AND and OR condition

$users = DB::table('users')
->where(function($query) { $query->where('col','=','')->orWhere('col','=','')->whereNull('col'); })
->where('col','=','')
->get();
user_143782240
Related