Dealing with null values in Laravel database query?

Question

I am finding that I often need to select a field, based on a condition other than the id.

So, $user = User::where('last_login', $lastLogin)->where('last_warning', $lastWarning)->get(); works perfectly.

That is until you set one of the where's to allow nulls (let's do last_login).

That is, it can either have a value or be null.

That means you need to use one of two function where() or whereNull() and to do that you need to break the chain, so it becomes

$user = User::where('last_warning', $lastWarning);

is_null($lastLogin) ? $user->whereNull('last_login') : $user->where('last_login', $lastLogin);

$user = $user->get();

I am wondering if where has a way to deal with this? as currently if you pass null through to where you get where column = null which doesn't work!

Solution 1

Two options:

Option 1:

if (is_null($lastLogin))
{
    $user = User::whereNull('last_login')->where('last_warning', $lastWarning)->get();
}
else
{
    $user = User::where('last_login', $lastLogin)->where('last_warning', $lastWarning)->get();
}

Option 2:

$user = User::where('last_login', (is_null($lastLogin) ? 'IS' : '=') ,$lastLogin)->where('last_warning', $lastWarning)->get();

Option two makes the query 'where last_login = x' or 'where last_login IS null'

Solution 2

You can try this:

User::where(function($query) use ($lastlogin)
        {
            if(is_null($lastLogin))
            {
                $query->whereNull('last_login');
            }
            else
            {
                $query->where('last_login', '=', $lastLogin);
            }
        })->get();

It is a good solution when dealing with long queries with more than one parameter.

Solution 3

As of Laravel 5.3 you are now able to ->where('column', null) to automatically produce WHERE column IS NULL.

If using variable, make sure that they have PHP null strict value.

Solution 4

You can use DB::raw() as well:

User::where('last_login', 'IS', DB::raw('null'))->where_last_warning($lastWarning)->get();

View more solutions

Comments

[Hailwood]

Is there ant reason that Where() doesn't have any logic to do that automatically if null is passed?

[Laurence]

Sorry - you'd have to as Taylor that. Post it as an issue/request on github perhaps...

[Hailwood]

Yeah, might fork, create the patch, and then submit the request with a pull request.