如何使用yii查询构建器编写此SQL查询

Question

我有这个SQL查询，它正在执行我想要的工作：

SELECT `Table1`.* FROM `Table1` 
LEFT JOIN `Table2` ON `Table1`.`idTable2` = `Table2`.`id` 
WHERE (`Table1`.`idOwner`=156 AND `Table2`.`enabled`=1 AND day(Table2.creationDate) <= 5 AND date_format(Table2.creationDate, '%Y-%m') = '2022-12') 
OR (`Table1`.`idOwner`=156 AND `Table2`.`enabled`=1 AND date_format(Table2.creationDate, '%Y-%m') != '2023-01' AND date_format(Table2.creationDate, '%Y-%m') != '2022-12')

我尝试将其复制为yii查询构建器，喜欢这样：

Table1::find()
   ->joinWith(['table2'])
   ->where(['Table1.idOwner' => $idOwner, 'Table2.enabled' => 1])
   ->andWhere(['<=', 'day(Table2.creationDate)', $expirationDay])
   ->andWhere(['=', "date_format(Table2.creationDate, '%Y-%m')", $previousExpirationMonthYear])
   ->orWhere(['Table1.idOwner' => $idOwner, 'Table2.enabled' => 1])
   ->andWhere(['!=', "date_format(Table2.creationDate, '%Y-%m')", $currentExpirationMonthYear])
   ->andWhere(['!=', "date_format(Table2.creationDate, '%Y-%m')", $previousExpirationMonthYear])
   ->all();

但是我打印了SQL此查询构建器以getrawsql（）生成，并且它返回了这个奇怪的方式：

SELECT `Table1`.* FROM `Table1` 
LEFT JOIN `Table2` ON `Table1`.`idTable2` = `Table2`.`id` 
WHERE (((((((`Table1`.`idOwner`=156) 
AND (`Table2`.`enabled`=1)) 
AND (day(Table2.creationDate) <= 5)) 
AND (date_format(Table2.creationDate, '%Y-%m') = '2022-12')) 
OR ((`Table1`.`idOwner`=156) 
AND (`Table2`.`enabled`=1))) 
AND (date_format(Table2.creationDate, '%Y-%m') != '2023-01')) 
AND (date_format(Table2.creationDate, '%Y-%m') != '2022-12')) 
AND (`Table1`.`idOwner`='156')

对不起，如果很难以这种方式阅读。

谁能像我想要的那样帮助我制作查询构建器吗？我会非常感谢

Answer 1

每当您使用and Where（）或or Where（）查询构建器采取现有条件并执行类似的事情时：

（现有条件）和（新条件）

or

（现有条件）或（新条件）

respectively.

So if you already have some complex condition and then you try to call

orWhere(new condition 1)
->andWhere(new condition 2)

You will get

（（复杂条件）或（新条件1））和（新条件2）。

但是在您的情况下，您需要得到类似的东西：

（复杂条件1）或（复杂条件2）

To get something like that you can build the first complex condition same way but you have to build second condition in one orWhere() call.或使其更可读性，您可以单个呼叫构建两个复杂条件：

Table1::find()
    ->joinWith(['table2'])
    ->where([
        'AND',
        ['Table1.idOwner' => $idOwner, 'Table2.enabled' => 1],
        ['<=', 'day(Table2.creationDate)', $expirationDay],
        ['=', "date_format(Table2.creationDate, '%Y-%m')", $previousExpirationMonthYear]
    ])->orWhere([
        'AND',
        ['Table1.idOwner' => $idOwner, 'Table2.enabled' => 1],
        ['!=', "date_format(Table2.creationDate, '%Y-%m')", $currentExpirationMonthYear],
        ['!=', "date_format(Table2.creationDate, '%Y-%m')", $previousExpirationMonthYear]
    ])->all();