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?
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.
You could merge two queries together:
$merged = $query_one->merge($query_two);
Using coalesce() converts null to 0:
$query = Model::where('field1', 1)
->whereNull('field2')
->where(DB::raw('COALESCE(datefield_at,0)'), '<', $date)
;
Source: Stackoverflow.com