CakePHP 3.6 Query Builder complex OR conditions issue

Question

I am trying to build a query using query builder with complex nested AND and OR conditions. Here is what I have written so far.

$cond_arr = array();
$cond_arr['VehicleBrandModels.status'] = 1;
$query = $this->VehicleBrandModels->find();
$query->hydrate(false);
$query->select($this->VehicleBrandModels);
$query->where($cond_arr);
$VBM_data = $query->toArray();

This will generate a query like below

SELECT * FROM vehicle_brand_models WHERE status = 1;

I want to generate a query with nested AND & OR conditions like below

SELECT * FROM vehicle_brand_models WHERE status = 1 AND ((overall_rating > 0 AND overall_rating < 2) OR (overall_rating >= 2 AND overall_rating < 4) OR (overall_rating >= 4 AND overall_rating <= 5));

Can anybody help to solve how to achieve this in CAKEPHP 3.0 Query builder?

This may be of use to you [CakePHP Documentation](https://book.cakephp.org/3.0/en/orm/query-builder.html#advanced-conditions) — Derek Fulginiti, May 08 '18 at 16:41

arilia · Accepted Answer · 2018-05-08T12:52:15.830

The simplest solution is the following

$cond_arr = [
    'VehicleBrandModels.status' => 1, 
    'OR' => [
        ['overall_rating >' =>  0, 'overall_rating <' => 2],
        ['overall_rating >=' => 2, 'overall_rating <' => 4],              
        ['overall_rating >=' => 4, 'overall_rating <=' => 5]
    ]
];

there is a more 'cake' way and if I'll have time I will post it.

But note that from what I see all your OR conditions overlap and you can simply do

 $cond_arr = [
    'VehicleBrandModels.status' => 1, 
    'overall_rating >' =>  0, 
    'overall_rating <=' => 5
];

edit: as primised here's the more cake way using query expressions

$query->where(function($exp, $q) {
    $exp = $exp->eq('VehicleBrandModels.status', 1);
    $conditions1 = $exp->and_([])
        ->gt('overall_rating ', 0)
        ->lte('overall_rating ', 2);
    $conditions2 = $exp->and_([])
        ->gt('overall_rating ', 2)
        ->lte('overall_rating ', 4);
    $conditions3 = $exp->and_([])
        ->gt('overall_rating ', 4)
        ->lte('overall_rating ', 5);
    $orConditions = $exp->or_([$conditions1, $conditions2, $conditions3]);
    $exp->add($orConditions);
    return $exp;
});

still some conditions are overlapping

Thanks a lot. Saved me a lot of time. @arilia – Chinmaya Panigrahi May 09 '18 at 05:43 — Chinmaya Panigrahi, May 09 '18 at 05:43

CakePHP 3.6 Query Builder complex OR conditions issue

1 Answers1