Hi, i have a problem with MySQL 5.7.1 and Yii
MySQL 5.7 by default enable ONLY_FULL_GROUP_BY
If i have a table like this
id | account | ... othersvals |
1 | TEST | ....
2 | HOUSE | ...
3 | TEST | ...
In older version of MySQL the next SQL Statement work fine
SELECT id, account FROM accounts GROUP BY account;
SELECT * FROM accounts GROUP BY account;
But in MySQL 5.7 get this error
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.accounts.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
The SQL being executed was: SELECT * FROM accounts
ORDER BY account
It was correct according to http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sqlmode_only_full_group_by
So my problem with Yii2 is:
'filter'=>\yii\helpers\ArrayHelper::map(\backend\models\Accounts::find()->asArray()->orderBy('account')->groupBy('account')
it crash in MySQL 5.7 because it create this query: SELECT * FROM accounts
ORDER BY …
How can i made this query work with Yii2 and MySQL 5.7
I want to try http://johnemb.blogspot.mx/2014/09/adding-or-removing-individual-sql-modes.html but i do not know how
Warm Regards