mtmacdonald mtmacdonald - 1 month ago 6
MySQL Question

How to add brackets around WHERE conditions with Laravel query builder

I'm using the Laravel query builder to dynamically filter data based on a user's filter selections:

$query = DB::table('readings');
foreach ($selections as $selection) {
$query->orWhere('id', $selection);
}
$query->whereBetween('date', array($from, $to));
$query->groupBy('id');


When I examine the SQL, I get something like this:

select count(*) as `count` from `readings` where `id` = 1 or id` = 2 and `date` between "2013-09-01" and "2013-09-31" group by `id`;


But what I need is something like this (with brackets around the or statements):

select count(*) as `count` from `readings` where (`id` = 1 or id` = 2) and `date` between "2013-09-01" and "2013-09-31" group by `id`;


How do I add brackets around WHERE conditions with Laravel query builder?

Answer

Solved this myself by using a closure, as described in Parameter Grouping in the query builder documentation.

 $query = DB::table('readings');
 $this->builder->orWhere(function($query) use ($selections)
 {
    foreach ($selections as $selection) {
       $query->orWhere('id', $selection);
    }
 });
 $query->whereBetween('date', array($from, $to));
 $query->groupBy('id');