Laravel Eloquent where field is X or null

php laravel eloquent

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?

simhumileco

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.

This saved me some frustration! Thanks.

can it be done by something like: whereIn('column',['value',null]) ?

@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?

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.

@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"

kaleazy

You could merge two queries together:

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

ebelendez

Using coalesce() converts null to 0:

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

Dip Roy

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

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US