[FIXED] CakePHP 3 - unable to generate a query with WHERE...OR conditions

Issue

CakePHP 3.7

I'm trying to generate a query which uses a WHERE...OR pattern. The equivalent in MySQL - which executes and gives the results I want is:

SELECT * FROM groups Groups WHERE (regulation_id = 1 AND label like '%labelling%') OR (id IN(89,1,8,232,228,276,268,294));

I've read the Advanced Conditions (https://book.cakephp.org/3.0/en/orm/query-builder.html#advanced-conditions) part of the documentation but can't generate that query.

Assume the Table class is Groups I have this:

$Groups = TableRegistry::getTableLocator()->get('Groups');

$groups_data = $Groups->find('all')->where(['regulation_id' => 1);

$groups_data = $groups_data->where(['label LIKE' => '%labelling%']);

This produces the first segment of the WHERE statement, i.e.

SELECT * FROM groups Groups WHERE (regulation_id = 1 AND label like '%labelling%')

However I can't see how to attach the OR condition, especially since orWhere() is deprecated.

So I've tried this - which is even given as an example in the docs:

$in_array = [89,1,8,232,228,276,268,294]; // ID's for IN condition

$groups_data = $groups_data->where(['OR' => ['id IN' => $in_array]]);

But this just appends an AND to the inside of my existing SQL:

SELECT * FROM groups Groups WHERE (regulation_id = 1 AND label like '%labelling%' AND id IN(89,1,8,232,228,276,268,294);

Which does not yield the correct results as the syntax isn't what's required to run this query.

How do you "move out" of the WHERE and append an OR condition like in the vanilla query?

I made several attempts using QueryExpression as per the docs, but all of these produced PHP Fatal Errors saying something to do with the Table class - I doubt this was on the right lines anyway.

Solution

"moving out" is a little tricky, you have to understand that internally the conditions are pushed into a \Cake\Database\Expression\QueryExpression object which by default uses AND to concatenate the statements, so whatever you push on to that, will be added using AND.

When you create OR statements, being it implicitly with the shown nested array syntax, or explicitly by using the expression builder, this creates a separate, self-contained expression, where its parts are being concatenated using OR, it will compile itself (and since there's only one condition, you don't see any OR's), and the result will be used in the parent expression, which in your case is the main/base expression object for the queries where clause.

Either pass the whole thing at once (being it via array syntax or expressions), eg:

$groups_data->where([
    'OR' => [
        'AND' => [
            'regulation_id' => 1,
            'label LIKE' => '%labelling%'
        ],
        'id IN' => $in_array
    ]
]);

and of course you could build that array dynamically if required, or, if you for some reason need to use separate calls to where(), you could for example overwrite the conditions (third parameter of where()), and include the current ones where you need them:

$groups_data->where(
    [
        'OR' => [
            $groups_data->clause('where'),
            'id IN' => $in_array
        ]
    ],
    [], 
    true
);

Answered By - ndm