groupByでエラーになる原因は?

※本サイトはアフィリエイトリンクを掲載しています。
Laravel

こんにちは。web系エンジニアのカズです。

groupByでエラーになり、過去躓いたことがある内容を記事にしました。

私が過去遭遇したエラー内容はこちらです。

 

エラー内容

Illuminate\Database\QueryException

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘カラム名’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select * from `テーブル名` group by `カラム名`)

 

原因

エラーになってしまう原因は、selectするカラムとgroupByでグループ化するカラムが一致していないとエラーになります。

エラーがでるソースコード例

membersテーブル

id name age class
1 テスト太郎 25 Aクラス
2 テスト次郎 27 Bクラス
3 テスト小太郎 20 Aクラス
例えば、classが同じレコードはグループ化して1つにまとめたい。
Member::groupBy('class')->get();
この場合エラーになります。selectするカラムが指定されずget()しているので、全てのカラムがselect対象となりgroupByするカラムと一致していません。
Member::select('id','name')->groupBy('class')->get();
こちらは当然エラーになります。グループ化したいclassカラムがselect対象になっていないためです。

 
おすすめ本


 

解決方法

groupByに指定するカラムとselectに指定するカラムを一致させる

Member::select('class')->groupBy('class')->get();
こうすれば、エラーを回避できます。
しかし、実際このようなソースは汎用性がありません。
グループ化するためにselectするカラムがclassカラムしか取得できなくなり、他のカラムが取得できなくなってしまいます。
では、どうすれば良いか。解決例を二つ記載します。

 

WhereInの中にサブクエリを作成する

$members = Member::whereIn(DB::raw('(class, id)'), function ($subquery)
{
    $subquery->select('class',DB::raw('min(id)'))
        ->from('members')
        ->groupBy('class');
});
$membersは、全てのカラムが取得されclassでグループ化される形になります。

 

only_full_group_byを解除する(おすすめしない)

mysqlが厳格なSQLを要求しないように設定を変えることができます。

Laravelのconfigファイルに設定があります。

config/database.php

'mysql' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,              こちらをfalseにします。
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],

コメント