如何用CakePHP 3.x查询构建器表达OR条件？

Question

I am trying to build a query in cakephp 3.X with the following structure : A and B and C and [D or E] where A,B, C, D, E contains an array of values joined with OR conditions like

$schools = $schools->where(['A  IN' => $arrayOfAOptions]), 
$schools = $schools->where(['B IN' => $arrayOfBOptions]),
$schools = $schools->where(['C IN' => $arrayOfCOptions])

// want this to be firstly joined with OR before joining other conditions with AND
$schools = $schools->where(['D IN' => $arrayOfCOptions]) 
//or
$schools = $schools->where(['E IN' => $arrayOfCOptions])

the problem here is that , I could not get fields D and E to be isolated as OR and join others with AND

in cakephp 2, i managed to achieve it with the following codes

$queryConditions = array(
  'or'=> array_merge(
    array(
      'School.D' =>  $this->Session->read('conditions.D')
    ), 
    array(
      'School.E' => $this->Session->read('conditions.E')
    )
  )
);

before merging it to the rest on the conditions Any help would be appreciated

Answer 1

You can use the same style as in CakePHP 2.x, ie pass your conditions nested under the OR key. You can also wrap this in a single where() call.

$schools->where([
    'A IN' => $arrayOfAOptions,
    'B IN' => $arrayOfBOptions,
    'C IN' => $arrayOfCOptions,
    'OR' => [
        'D IN' => $arrayOfDOptions,
        'E IN' => $arrayOfEOptions
    ]
]);

You could also use orWhere(), but it has been deprecated lately as its behavior is a kinda fragile.

Finally, you can use expressions to create complex conditions. The or_() expression method can be used to create OR conditions:

$schools->where(function ($expr) use ($arrayOfAOptions, $arrayOfBOptions) {
    return $expr->or_([
        'A IN' => $arrayOfAOptions,
        'B IN' => $arrayOfBOptions,
    ]);
});

See also Cookbook > Database Access & ORM > Query Builder > Advanced Conditions