So schreiben Sie diese SQL-Abfrage mit Yii Query Builder

Question

Ich habe diese SQL-Abfrage, die das tut, was ich will:

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')

Ich habe versucht, es als Yii-Abfrage-Builder wie folgt zu kopieren:

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();

Aber ich habe das von diesem Abfrage-Generator generierte SQL mit getRawSql() ausgedruckt und es kam auf diese seltsame Weise zurück:

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')

Es tut mir leid, wenn das schwer zu lesen ist.

Kann mir jemand helfen, den Abfrage-Generator so zu gestalten, wie ich es möchte? Ich wäre dankbar

Answer 1

每当您使用 andWhere() 或 orWhere() 时，查询生成器都会采用现有条件并执行如下操作：

或

分别。

因此，如果您已经遇到一些复杂的情况，然后尝试致电

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

你将会得到

但就你而言，你需要得到类似的东西：

要获得类似的结果，您可以以相同的方式构建第一个复杂条件，但必须在一个 orWhere() 调用中构建第二个条件。或者为了使其更具可读性，您可以在单个调用中分别构建两个复杂的条件：

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();