How to write this SQL query using Yii Query Builder

Question

I have this SQL query that 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 copying it as a Yii query builder like this:

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 generated by this query builder using getRawSql() and it returned this weird 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 this is hard to read.

Can anyone help me make the query builder the way I want? I will be very grateful

Answer 1

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

or

respectively.

So if you have faced some complex situation then try calling

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

You will get

But in your case you need to get something like:

To achieve similar results, you can construct the first complex condition in the same way, but the second condition must be constructed in a single orWhere() call. Or to make it more readable, you can build two complex conditions separately in a 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();