AND condition followed by OR between BETWEEN in CakePHP 3.4x

Question

What I am trying to get:

SELECT col,col,col FROM x WHERE id = :c0 AND ((colx BETWEEN :c1 AND :c2) OR (colx BETWEEN :c3 AND :c4))

What I tried:

$finalList = $finalList->find()->where(['id' => $id]);
foreach($dataArray as $y):
$finalList = $finalList->orWhere(function($expressions) use ($y['min'], $y['max']) {
    return $expressions->between('colx', $y['min'], $y['max']);
}
endforeach;

What I am getting:

SELECT col,col,col FROM x WHERE id = :c0 OR colx BETWEEN :c1 AND :c2 OR colx BETWEEN :c3 AND :c4

I want id to be required and OR between BETWEEN

score 2 · Accepted Answer · answered Jul 24 '17 at 15:03

That's how orWhere() works. Quote from the API docs:

It is important to notice that when calling this function, any previous set of conditions defined for this query will be treated as a single argument for the OR operator. This function will not only operate the most recently defined condition, but all the conditions as a whole.

It's not overly straight forward though, which is why orWhere() has recently been deprecated.

To get this working using orWhere(), you'd have to apply the where() (or andWhere()) after orWhere(), ie:

$finalList = $finalList->find();
foreach($dataArray as $y) {
    // ... apply orWhere()
}
$finalList->where(['id' => $id]);

Alternatively use the expression builder all the way through:

$finalList = $finalList->where(function ($exp) use ($dataArray) {
    $or = $exp->or_([]);
    foreach($dataArray as $y) {
        $or->between('colx', $y['min'], $y['max']);
    }

    return $exp->add(['id' => 1, $or]);
});

AND condition followed by OR between BETWEEN in CakePHP 3.4x

1 Answers1