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
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Whenever you use
andWhere()ororWhere(), 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
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], ['orWhere([ 'AND', ['Table1.idOwner' => $idOwner, 'Table2.enabled' => 1], ['!=', "date_format(Table2.creationDate, '%Y-%m')", $currentExpirationMonthYear], ['!=', "date_format(Table2.creationDate, '%Y-%m')", $previousExpirationMonthYear] ])->all();