CODEWITHSUNDEEP
phpmysqlnulllaravellaravel-4
Hailwood
Hailwood

Reputation: 92581

Dealing with null values in Laravel database query?

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!

Upvotes: 3

Views: 23694

Answers (4)

Marco Aurélio Deleu
Marco Aurélio Deleu

Reputation: 4367

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.

Upvotes: 1

André Luiz Müller
André Luiz Müller

Reputation: 195

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.

Upvotes: 3

Gabriel Koerich
Gabriel Koerich

Reputation: 587

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

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

Upvotes: 1

Laurence
Laurence

Reputation: 60048

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'

Upvotes: 5

Related Questions