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!

Laurence · Accepted Answer · 2013-02-20 16:42:41Z

5

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'

answered Feb 20, 2013 at 16:42

Laurence

60.3k22 gold badges178 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hailwood Over a year ago

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

Laurence Over a year ago

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

Hailwood Over a year ago

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

André Luiz Müller · Accepted Answer · 2015-01-16 13:46:51Z

3

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.

edited Jan 16, 2015 at 13:46

answered Jan 16, 2015 at 11:11

André Luiz Müller

1958 bronze badges

Comments

Gabriel Koerich · Accepted Answer · 2013-02-20 17:03:03Z

1

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

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

answered Feb 20, 2013 at 17:03

Gabriel Koerich

5872 silver badges6 bronze badges

Comments

Marco Aurélio Deleu · Accepted Answer · 2016-10-18 19:09:32Z

1

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.

answered Oct 18, 2016 at 19:09

Marco Aurélio Deleu

4,3774 gold badges38 silver badges63 bronze badges

Collectives™ on Stack Overflow

Dealing with null values in Laravel database query?

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related