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              - gt whereNull  field2               - gt where  datefield     lt     date              - gt 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

User · Accepted Answer

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.

User · Answer

You could merge two queries together    merged    query one- gt merge  query two

User · Answer

Using coalesce   converts null to 0   query   Model  where  field1   1      - gt whereNull  field2       - gt where DB  raw  COALESCE datefield at 0       lt     date

[php] Laravel Eloquent where field is X or null

Examples related to php

Examples related to laravel

Examples related to eloquent