CODEWITHSUNDEEP
phpmysqllaravel
Shiping
Shiping

Reputation: 1337

Laravel: how to mix 'and' and 'or' conditions in a where clause?

I've searched and found similar posts but none of them are the same as my case. Seems the Laravel documents don't show a case like mine though my case is quite common in a where clause of database queries.

The where clause in my case is like the following and I can't get it to work with where() and orwhere() methods and tricks people suggested in other posts. Any info appreciated. Thanks.

where a = 1 and b = 2 and (c = 3 or c = 4) and (d = 5 or d = 6)

Another level of complication is that I can combine all 'and' conditions into one single array and feed it into one where() method, but I have to group 'or' conditions into separate arrays and how many groups are dynamically collected in the codes, such as the following.

$and = [['a', '=', 1], ['b', '=', 2], ...];

if($cond1) $or['or1'] = [['c', '=', 3], ['c', '=', 4], ...];
if($cond2) $or['or2'] = [['d', '=', 5], ['d', '=', 6], ...];
if($cond3) $or['or3'] = [['e', '=', 7], ['e', '=', 8], ...];
...

PS: I like to thank all who replied (posts or comments). I've seriously considered all suggestions and tried some that could be applied to my case, but unfortunately none of them worked. So my solution is just to compile a raw where clause as shown in the example I gave above and used whereRaw() method. It's straight forward to do and worked quite well.

Upvotes: 1

Views: 1295

Answers (5)

aimme
aimme

Reputation: 6818

Since you have improved your question. Here is how you can achieve it.

Haven’t tested. How about something like this.

$ands = [['a', '=', 1], ['b', '=', 2], ...];

$query = Model::query();
foreach($and as $and) {
    $query = $query->where($and);
}

if($cond1) $ors['c'] = [3, 4, ...];
if($cond2) $ors['d'] = [5, 6, ...];
if($cond3) $ors['e'] = [7, 8, ...];
...

foreach($ors as $key => $or) {
    $query = $query->where(function($q) use ($key, $or) {
        $q->whereIn($key,$or);
     });
}

$query->get();

Upvotes: 1

aimme
aimme

Reputation: 6818

use whereIn() inside sub query. Havent checked, but I think this will work.

Model::where('a', '1')
        ->where('b', '2')
        ->where(function ($q) {
             $q->whereIn('c', ['3', '4']);
        })
        ->where(function ($q) {
             $q->whereIn('d', ['5', '6']);
        })->get();

Upvotes: 1

STA
STA

Reputation: 34838

If I got your question, then this receipe is for you :

$result = DB::table('table')
   ->where('a', 1)
   ->where('b', 2)
   ->where(function($query) {
      $query->where('c', 3)
            ->orWhere('c', 4)
   })
   ->where(function($query) {
      $query->where('d', 5)
            ->orWhere('d', 6)
   ->get();

Upvotes: 1

Martin Dimitrov
Martin Dimitrov

Reputation: 1304

Something like this should do the trick:

Model::where([
    ['a', '=', 1],
    ['b', '=', 2],
    [['c', '=', 3], 'OR', ['c', '=', 4]],
    [['d', '=', 5], 'OR', ['d', '=', 6]]
]);

Upvotes: 1

mmabdelgawad
mmabdelgawad

Reputation: 2545

You can do it like so

Model::where('a', '1')
    ->where('b', '2')
    ->where(function ($q) {
         $q->where('c', '3')->orWhere('c', '4');
    })
    ->where(function ($q) {
         $q->where('d', '5')->orWhere('d', '5');
    })->get();

Upvotes: 4

Related Questions