HOW TO resolve ONLY_FULL_GROUP_BY issue?

[administrator]

From MySQL 5.7 ONLY_FULL_GROUP_BY by default is turned on and it may lead to some unexpected behavioral of your script and show error.

Lets see why it happens.

For example we have following table "dates":

Code: Select all

id   name                 date
1    Jon Snow             2013-12-12
2    Jon Snow             2013-12-13
3    Daenerys Targaryen   2013-12-14

Now we run two same queries, one with empty sql_mode and second with ONLY_FULL_GROUP_BY

Code: Select all

SET @@sql_mode = "";
SELECT * FROM dates GROUP BY name;

SET @@sql_mode = "ONLY_FULL_GROUP_BY";
SELECT * FROM dates GROUP BY name;

In first case we get following result:

Code: Select all

id   name                 date                     
1    Jon Snow             2013-12-12
3    Daenerys Targaryen   2013-12-14

And in the second - an error. SQL error 1055 dates.id is not in GROUP BY. Which is logical, because id is implicitly present in SELECT.

So how do we need to write SQL to prevent this error?
The answer is below:

Code: Select all

SELECT MAX(date), name FROM dates GROUP BY name

Now... how can we do this in ActiveRecord?
The syntax is following:

Code: Select all

$table->findAll('
  array('select'=>'MAX(date)', 'groupBy'=>'name')
');

you may also use new function ANY_VALUE() for non-aggregated fields:
https://dev.mysql.com/doc/refman/5.7/en ... _any-value

- or -

Code: Select all

$result = SurveyParticipants::model()->count(array(
   'condition'   => $table.'.is_active = 1 AND '.$table.'.status = 2',
   'select'   => $table.'.survey_id',
   'count'      => '*',
   'groupBy'   => 'survey_id',
   'allRows'   => true
));

P.S. Of course, you mat turn off "ONLY_FULL_GROUP_BY" mode and leave your code impact.