CODEWITHSUNDEEP
mysqllaravel
mtmacdonald
mtmacdonald

Reputation: 15110

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?

Upvotes: 20

Views: 23198

Answers (5)

Fawaz Kindil
Fawaz Kindil

Reputation: 43

Sometimes you may need to group several "where" clauses within parentheses in order to achieve your query's desired logical grouping. In fact, you should generally always group calls to the orWhere method in parentheses in order to avoid unexpected query behavior. To accomplish this, you may pass a closure to the where method:

$users = DB::table('users')
           ->where('name', '=', 'John')
           ->where(function ($query) {
               $query->where('votes', '>', 100)
                     ->orWhere('title', '=', 'Admin');
           })
           ->get();

As you can see, passing a closure into the where method instructs the query builder to begin a constraint group. The closure will receive a query builder instance which you can use to set the constraints that should be contained within the parenthesis group. The example above will produce the following SQL:

select * from users where name = 'John' and (votes > 100 or title = 'Admin')

Upvotes: 3

Gonzalo Tito
Gonzalo Tito

Reputation: 321

Very useful, I use this:

->where(function ($query) use ($texto){
    $query->where('UPPER(V_CODIGO)', 'LIKE', '%'.Str::upper($texto).'%')
          ->orWhere('UPPER(V_NOMBRE)', 'LIKE', '%'.Str::upper($texto).'%');
});

Upvotes: 32

Furkan Mustafa
Furkan Mustafa

Reputation: 794

I couldn't find this in documentation, whereNested was what I was looking for. Hope it helps anybody.

$q->whereNested(function($q) use ($nameSearch) {
    $q->where('name', 'LIKE', "%{$nameSearch}%");
    $q->orWhere('surname', 'LIKE', "%{$nameSearch}%");
});

Note: This is on Laravel 4.2

Upvotes: 12

mtmacdonald
mtmacdonald

Reputation: 15110

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');

Upvotes: 9

No Results Found
No Results Found

Reputation: 102784

You can use WHERE IN here for the same effect:

$query = DB::table('readings');
$query->whereIn('id', $selection)
$query->whereBetween('date', array($from, $to));
$query->groupBy('id');

Upvotes: 2

Related Questions