Laravel Eloquent where field is X or null

Question

I have a table like this:

table
- field1: tinyint
- field2: varchar (nullable)
- datefield: timestamp (nullable)

Now I want to get all entries where field1 is 1, field2 is null and where datefield is smaller than X or null. I already tried something like this:

$query = Model::where('field1', 1)
            ->whereNull('field2')
            ->where('datefield', '<', $date)
            ->orWhereNull('datefield');

but thats not working. I always get every entry where datefield is null. It doesn't matter what the other fields are. I also tried to split it in 2 queries: First get every row where datefield is smaller than X or null and then (based on it) get every field where field1 is 1 and field2 is null.

The result was the same. Any idea how to do this?

score 215 · Accepted Answer · edited Aug 11 '17 at 19:04

215

It sounds like you need to make use of advanced where clauses.

Given that search in field1 and field2 is constant we will leave them as is, but we are going to adjust your search in datefield a little.

Try this:

$query = Model::where('field1', 1)
    ->whereNull('field2')
    ->where(function ($query) {
        $query->where('datefield', '<', $date)
            ->orWhereNull('datefield');
    }
);

If you ever need to debug a query and see why it isn't working, it can help to see what SQL it is actually executing. You can chain ->toSql() to the end of your eloquent query to generate the SQL.

edited Aug 11 '17 at 19:04

simhumileco

31,877
16
137
115

answered Apr 02 '16 at 10:29

James

15,754
12
73
91

4

This saved me some frustration! Thanks. – Bhargav Nanekalva Mar 14 '17 at 19:00
can it be done by something like: whereIn('column',['value',null]) ? – Shafi Feb 28 '18 at 09:34
@MASh not sure. I wrote this quite some time ago and it was the best approach at the time. Why not give it a try? – James Feb 28 '18 at 09:40
I use the approach you wrote here normally. Tried and it fetched nothing from database. May be database engines treat null different than value. I wanted to know if there is way to pass the null within the array. – Shafi Feb 28 '18 at 10:06
@MASh: Yes databases treat values and NULL differently (the string "null" is a normal value). Normal conditions where you compare values have a defined result. Comparisons on NULL are undefined because NULL represents something unknown and comparing a value with something unknown can't lead to a result. To check if something is NULL within a query use "IS" -> "where field1 IS NULL" – chilly Mar 24 '18 at 15:47
I haven't seen 'toSql' before, thank you for the extra information! – Virginia Mar 25 '19 at 07:23
Took me 3 hours to figure this out! Haven't been coding for many months and this is what happens! – pixelscreen Sep 18 '19 at 08:38
I've just noticed on Laravel 5.6 at least, that `where('column', '=', null)` is automatically translated to `[where] column is null`. So in many instances where this double check is used (equals value or is null), Laravel will do it for you out of the box. I'm not entirely keen on it working like that, but that is what we have. – Jason Dec 17 '19 at 12:32

score 6 · Answer 2 · answered Mar 08 '19 at 04:17

6

You could merge two queries together:

$merged = $query_one->merge($query_two);

answered Mar 08 '19 at 04:17

kaleazy

5,922
2
47
51

score 4 · Answer 3 · answered Jul 18 '20 at 02:00

4

Using coalesce() converts null to 0:

$query = Model::where('field1', 1)
    ->whereNull('field2')
    ->where(DB::raw('COALESCE(datefield_at,0)'), '<', $date)
;

answered Jul 18 '20 at 02:00

ebelendez

848
2
12
22

score 2 · Answer 4 · answered Mar 24 '22 at 13:08

If you are confused about where to put the get()/first() for getting the collection or a single row here is the way:

$query = Model::where('field1', 1)
    ->whereNull('field2')
    ->where(function ($query) {
        $query->where('datefield', '<', $date)
            ->orWhereNull('datefield');
    }
)->get();

Laravel Eloquent where field is X or null

4 Answers4

Linked

Related