Eloquent select rows with empty string or null value

Question

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

Just a tip for future users, the solution is whereNull('column'), as you can see in https://laravel.com/docs/5.7/queries#unions — Marcelo Agimóvel, Jan 10 '19 at 12:16

score 66 · Accepted Answer · edited Nov 12 '22 at 06:01

66

Try using orWhereNull for the second clause:

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

Or if you have multiple conditions in the query, you have to wrap the two in a closure:

    $users = DB::table('users')
             ->where(function(\Illuminate\Database\Eloquent\Builder $query) {
                $query->where('col', '')->orWhereNull('col');
            })
            ->where('col2','val2')
            ->get();

edited Nov 12 '22 at 06:01

raveren

17,799
12
70
83

answered Nov 28 '15 at 04:08

Jon

2,277
2
23
33

5

How would we change the where function incase if we have multiple conditions ? – Nevermore Jan 02 '17 at 07:45
5

@Nevermore Use a closure: `DB::table('users')->where(function($query) { $query->where(...)->orWhere(...)->orWhere(...); })->where(...)->where(...)->get();`. This will encapsulate the closure based conditions within parentheses in the rendered SQL. – Soulriser Mar 23 '18 at 19:27
1

Adding a model scope method can make using closures easier: usage: $model->whereIsEmpty($column1)->whereIsEmpty($column2); implementation: public function scopeWhereIsEmpty($query, $column) { return $query->where( function ($query) use ($column) { $query->where($column, '')->orWhereNull($column); } ); } – Jarrett Barnett Dec 10 '19 at 20:46

score 3 · Answer 2 · answered Jun 08 '20 at 20:57

3

How about this:

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

This way you can check both conditions at the same time

answered Jun 08 '20 at 20:57

Hugo Escobar

31
2

score 1 · Answer 3 · answered Jun 17 '21 at 06:09

1

Try this query:

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

answered Jun 17 '21 at 06:09

Yasin Patel

5,624
8
31
53

score 1 · Answer 4 · answered Sep 02 '22 at 12:39

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

score 1 · Answer 5 · answered Dec 01 '22 at 08:02

The below solution is tested on LARAVEL 9. Solution is to add to the App\Providers\AppServiceProvider this piece of code:

class AppServiceProvider extends ServiceProvider
{
    public function boot()
    {
        ///...
        Builder::macro('whereNullOrEmpty', function ($field) {
            return $this->where(function ($query) use ($field) {
                return $query->where($field, '=', null)->orWhere($field, '=', '');
            });
        });

        Builder::macro('whereNotNullOrEmpty', function ($field) {
            return $this->where(function ($query) use ($field) {
                return $query->where($field, '<>', null)->where($field, '<>', '');
            });
        });
    }
    //...
}

and then call it like this:

if($nullOrEmpty){
    $builder->whereNullOrEmpty('col');
}else{
    $builder->whereNotNullOrEmpty('col');
}

You must call both commands:

where($field, '<>', null);
where($field, '<>', '');

because the MySql is handling empty strings and nulls different than a PHP

you also need to wrap those two commands with the closure to tell Eloquent to put generated code in parentheses and isolate MySql code:

(where col = null or where col = '')

score -4 · Answer 6 · answered Dec 20 '13 at 15:35

-4

Not sure, but this might work:

$user->albums()->where_in('col', array(NULL,''));

answered Dec 20 '13 at 15:35

Sim0n222

106
4

5

NULL cannot be compared using `=` operator, so this won't work. Also, the method's name is `whereIn`, rather than `where_in`. Could've tested it, instead of writing "not sure, but this might work"… – jsphpl Jul 31 '17 at 13:37
2

@jsphpl Your comment is correct of course, but it should be noted that if you run `whereIn('col', [null, ''])` on a `Collection` object rather than a query builder, it _will_ work. Example: `$user->albums->whereIn('col', [null,'']);`. Note the use of the magic relational property `albums`, which returns a collection, and not `albums()`, which returns a query builder instance. – Soulriser Mar 23 '18 at 19:23

Eloquent select rows with empty string or null value

6 Answers6

Linked