How to write this SQL query with Yii Query Builder-CodePudding

I have this SQL query which is doing what I want:

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

I tried replicating it as a Yii Query Builder like that:

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

But I printed the SQL this Query Builder generates with getRawSql() and it returns in this strange way:

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

Sorry if its difficult to read it that way.

Can anyone help me to make the Query Builder like the way I want? I would be very appreciated

CodePudding user response：

Whenever you use andWhere() or orWhere() the query builder takes existing conditions and do something like this:

(existing conditions) AND (new conditions)

(existing conditions) OR (new conditions)

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

((complex condition) OR (new condition 1)) AND (new condition 2).

But in your case you need to get something like:

(complex condition 1) OR (complex condition 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. Or to make it more readable you can build both complex conditions each in single 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();