cakephp 在查询生成器中使用OR并立即加载

ukqbszuj  于 2022-11-12  发布在  PHP
关注(0)|答案(1)|浏览(188)

CakePHP版本:4.1.4

简介

我正在尝试使用查询构建器构建复杂的where子句并使用快速加载。

查询语句

我在xampp环境中使用10.4.14-MariaDB作为数据库服务器,使用PHP 7.4.10版。

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;

我在尝试1和2中引用的信息可以在这里找到。

尝试1

我通常在查询构造中使用where then和Where,所以我的第一个尝试是:

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

尝试2

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

我在尝试3和4中引用的信息可以在这里找到。

尝试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]]
        ]);
});

尝试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]]
        ]);
});

尝试摘要

我的所有尝试都没有返回所需的结果集。如果删除OR条件,则返回预期的结果集。

问题

我如何使用查询生成器构造上面的SQL查询,使其返回所需的结果集?
谢谢你Z

tag5nh1u

tag5nh1u1#

我不确定您需要哪种查询,下面是两个示例:

// 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],
        ],
    ]);

相关问题