I am using several scope functions in an eloquent model to retrieve data, and in one of the functions I need to form an and and an or clause, so I have something like this:
public function scopeApprovalDate($query)
{
$beg = strftime('%Y-%m-%d %T', time() - 7776000);
$end = strftime('%Y-%m-%d %T', time());
return $query->whereBetween('approval_date', array($beg, $end))->orWhere('approval_date', 'IS', 'NULL');
}
This produces the following query:
select * from `application` where `sales_rep` = ? and `approval_date` between ? and ? or `approval_date` = ? order by `bill_name` asc
[bindings] => Array
(
[0] => John Doe
[1] => 2014-10-11 15:32:29
[2] => 2015-01-09 14:32:29
[3] => IS
)
The orWhere() function is not properly handling the value for the operator and is instead seeing it as a value.
If I change to
->orWhere('approval_date', '=', 'NULL')
then the orWhere function properly interprets the 2nd parameter as the operator, however in MySQL when querying for a NULL value you must use the IS operator to get proper results. Using equals sign does not return the same record set.
There does not appear to be an orWhereNull() function although there is a whereNull() function, but that will create an AND condition in my where clause and I need it to be an OR.
Additionally, I need to encapsulate the BETWEEN and the IS NULL check on the date field in parenthesis otherwise the or condition will bring back records for sales reps other than specified in another scope function.
Is there a way to do this with scope functions in eloquent?
The end result query should look like this:
select * from application
where sales_rep = 'John Doe'
and (approval_date between '2014-10-11 15:25:07' and '2015-01-09 14:25:07'
or approval_date IS NULL)
order by `bill_name` asc;
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community