CODEWITHSUNDEEP
phplaraveleloquentormrelationship
Jonathan de M.
Jonathan de M.

Reputation: 9828

Eloquent select rows with empty string or null value

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

Upvotes: 22

Views: 81072

Answers (6)

Sim0n222
Sim0n222

Reputation: 104

Not sure, but this might work:

$user->albums()->whereIn('col', [null,'']);

Upvotes: -5

fico7489
fico7489

Reputation: 8560

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 = '')

Upvotes: 1

Jon
Jon

Reputation: 2377

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

Upvotes: 66

Costel-Irinel Viziteu
Costel-Irinel Viziteu

Reputation: 46

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

Upvotes: 1

Yasin Patel
Yasin Patel

Reputation: 5731

Try this query:

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

Upvotes: 2

Hugo Escobar
Hugo Escobar

Reputation: 31

How about this:

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

This way you can check both conditions at the same time

Upvotes: 3

Related Questions