Eloquent adds "is null" condition to a query and messes the results

Hello,

I have the query that you can read bellow:

Foo::where('some_status', 1)
            ->where(function ($query) {
                $query->where('other_status', 0)
                    ->orWhere([
                        ['other_status', 2],
                        [DB::raw('DATEDIFF(now(), other_status_updated_at) <= 2')]
                    ]);
            })->get();

This gets translated to:

select * from `foo` where `some_status` = ? and (`other_status` = ? or (`other_status` = ? and DATEDIFF(now(), other_status_updated_at) <= 2 is null))

I don't understand where that "is null" at the end is coming from and unfortunately it messes the results.

How can I change the query so I won't have the "is null" added?

Thank you.

RamjithAp

6 years ago

Level 10

Add this to your query

whereNotNull("column_name")

bobbybouwmann

6 years ago

Best Answer

Level 88

That's because you either need to use orWhereRaw or pass in the 2 as the argument

$query->where('other_status', 0)
    ->orWhere([
        ['other_status', 2],
        [DB::raw('DATEDIFF(now(), other_status_updated_at)'), '<=', 2]
    ]);

// OR
$query->where('other_status', 0)
    ->orWhere('other_status', 2),
    ->orWhereRaw('DATEDIFF(now(), other_status_updated_at) <= 2');

2 likes

bobbybouwmann

6 years ago

Level 88

@ramjithap That is not removing the is null from the query ;)

cosminc

6 years ago

Level 9

Thank you @bobbybouwmann, using [DB::raw('DATEDIFF(now(), other_status_updated_at)'), '<=', 2] does the trick :)

Please or to participate in this conversation.