Using OR in the query builder with eager loading

Question

CakePHP Version: 4.1.4

Introduction
I'm trying to use the query builder to build complex where clauses and use eager loading.

SQL
I'm using 10.4.14-MariaDB as the database server and PHP version 7.4.10 in an xampp environment. The following query returns the desired result set when ran in xampp.

SELECT *
FROM
    tasks
JOIN users ON
    users.id = tasks.user_id
WHERE tasks.status = 1
AND tasks.account_id = 1393
AND users.authorised' = 'yes'
OR users.area_id = 1346;

The information I've referenced for attempt 1 and 2 can be found here.

ATTEMPT 1
I normally use where then andWhere in the query construction so my first attempt was:

$query = $Tasks->find()
    ->contain(['Users'])
    ->where([
        'Tasks.status' => 1,
    ])
    ->andWhere([
        'Tasks.account_id' => 1393,
        'Users.authorised' => 'yes',
        'OR' => [['Users.area_id' => 1346]]
    ]);

ATTEMPT 2

$query = $Tasks->find()
    ->contain(['Users'])
    ->where(['Tasks.status' => 1])
    ->where(['Tasks.account_id' => 1393])
    ->where([
        'Users.authorised' => 'yes'
        'OR' => [['Users.area_id' => 1346]]
    ]);

The information I've referenced for attempt 3 and 4 can be found here.

ATTEMPT 3

$query = $Tasks->find()->contain('Users', function (Query $q) {
    return $q
        ->where(['Tasks.status' => 1])
        ->andWhere([
            'Tasks.account_id' => 1393,
            'Users.authorised' => 'yes',
            'OR' => [['Users.area_id' => 1346]]
        ]);
});

ATTEMPT 4

$query = $Tasks->find()->contain('Users', function (Query $q) {
    return $q
        ->where(['Tasks.status' => 1])
        ->where(['Tasks.account_id' => 1393])
        ->where([
            'Users.authorised' => 'yes',
            'OR' => [['Users.area_id' => 1346]]
        ]);
});

ATTEMPTS SUMMARY
None of my attempts returned the desired result set. If I remove the OR condition the expected result set is returned.

QUESTION
How can I construct the SQL query above which does return the desired result set using the query builder.

Thanks Z.

user_1127933 · Answer

I'm not sure what kind of query you want, here are two examples:

// WHERE (tasks.status = 1 AND tasks.account_id = 1393 AND users.authorised' = 'yes') OR users.area_id = 1346;

$query = $Tasks->find()
    ->contain(['Users'])
    ->where([
        'OR' => [
            ['Tasks.status' => 1,'Tasks.account_id' => 1393, 'Users.authorised' => 'yes'],
            ['Users.area_id' => 1346],
        ],
    ]);

// WHERE tasks.status = 1 AND tasks.account_id = 1393 AND (users.authorised' = 'yes' OR users.area_id = 1346);

$query = $Tasks->find()
    ->contain(['Users'])
    ->where([
        'Tasks.status' => 1,'Tasks.account_id' => 1393, 
        'OR' => [
            ['Users.authorised' => 'yes'],
            ['Users.area_id' => 1346],
        ],
    ]);

Using OR in the query builder with eager loading

1 Answers