Help to write this SQL query as Query Builder

Yii 2.0 General Discussions
1

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

1 Like
2

Can you pinpoint where you are failing.

1 Like
3

Basically what’s happening is, for some reason the Query Builder is only getting data after the ->orWhere(['Table1.idOwner' => $idOwner, 'Table2.enabled' => 1]), maybe because of the way Query Builder use parentheses. If I erase,

->orWhere(['Table1.idOwner' => $idOwner, 'Table2.enabled' => 1])
   ->andWhere(['!=', "date_format(Table2.creationDate, '%Y-%m')", $currentExpirationMonthYear])
   ->andWhere(['!=', "date_format(Table2.creationDate, '%Y-%m')", $previousExpirationMonthYear])

The first part of the where condition is working, it even seems like Query Builder is considering only the conditions after the orWhere

1 Like
4

In the SQL Query that I wrote (which is working like intended), I put the first set of conditions inside a pair of parentheses and the second set of conditions (the ones from OR) inside a separated pair of parentheses

1 Like
5

I actually found what the error is. Let’s compare the SQL I wrote:

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

You can see all those conditions are inside the OR parentheses’. While in the SQL Query Builder does:

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

The OR parentheses’ are closed in the AND (Table2.enabled=1))) line, which means the rest of the conditions aren’t inside this OR. I don’t know how to solve this with Query Builder.

1 Like
6

You may want to separate out the common conditions into a single and clause. The common conditions are:

table1`.`idowner` = 156 AND `table2`.`enabled` = 1`

Then use the below format for the rest of the conditions.
->andWhere([‘or’, [ … conditions …])

I know this is not the full solution, but hopefully this approach works.

1 Like
7 
$query->orWhere([
    'and',
    ['Table1.idOwner' => $idOwner],
    ['Table2.enabled' => 1],
    ['!=', "date_format(Table2.creationDate, '%Y-%m')", $currentExpirationMonthYear],
    ['!=', "date_format(Table2.creationDate, '%Y-%m')", $previousExpirationMonthYear]
]);

You can write AND inside OR condition

1 Like
8

date_format(Table2.creationDate, ‘%Y-%m’)
Try not to convert in the query statement, and convert the input conditions of the query into the format supported by field, which will have better performance.

1 Like