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.