SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY

Cakephp4: SQL扱い時に遭遇したエラー

 error
SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'database_name.table.columns'; this is incompatible with sql_mode=only_full_group_by

調べてみると、どうやら Mysql5.7 から ONLY_FULL_GROUP_BY がデフォルトでONになっている模様。

例えば GROUP BY に指定がないカラムを SELECT のフィールド指定するとエラーとなります。

SELECT name, tel, MAX(created) FROM hoges GROUP BY name;

この例の場合は tel のカラムが GROUP BY に指定がないのでエラーとなる。

対策としては、sql_mode() を変更するか、ANY_VALUE() を使用して非集計カラムを参照することが考えられます。

今回の自分の場合は Cake2 からの移行で今後たくさんの SQL を移行する可能性が高いし、その中で同じケースと遭遇すると予想されるので sql_mode を変更する作戦に。

対策をケースにより選択すると良いと思います。

Cakephp4: sql_modeを変更する

config/app.php

// Datasourceの内容は環境による
'Datasources' => [
    'default' => [
        'className' => Connection::class,
        'driver' => Mysql::class,
        'init' => ["SET sql_mode = 'ONLY_FULL_GROUP_BY, 略"], //  ONLY_FULL_GROUP_BYを削除すればよろし。
],

とすることで非集計のカラムも選択できるようになりました。