こんにちは❗フリーランス・コンサルタント＆エンジニアの九保すこひです。

さてさて、これは開発者がたまに遭遇する「あるある」な話かもしれませんが、DBからGROUP BYでデータ取得するとき以下のようなエラーが発生したことはないでしょうか。

Syntax error or access violation: 1055 Expression #（数字） 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 （フィールド名）, id from （テーブル名） group by （グループ化フィールド名）)

このことを個人的に「ONLY_FULL_GROUP_BY問題」と呼んでいるのですが、なぜこのエラーが発生するのかを知っておかないと、長時間ハマってしまうこともあったりします。

そこで！

今回はGROUP BYしたときのエラーについてご紹介します。
ぜひ皆さんのお役に立てると嬉しいです😊✨

「かく言う私もよくハマってました😂」

1 どんな時にエラーになる？
2 なぜエラーになるのか？
3 解決するには
4 おわりに

どんな時にエラーになる？

例えば、次のようなテーブルがあるとします。

このテーブルには、各ユーザーが投稿した内容が保存されています。

では、「ユーザーごとの最新投稿」を取得する場合を考えてみましょう。
おそらく一番に思いつくのはこんなSQLではないでしょうか。

SELECT *, MAX(id) FROM posts GROUP BY user_id

しかし、残念ながらこの場合はエラーになります。（注：MySQLの設定にもよります）

では、次の場合はどうでしょう。

SELECT user_id, MAX(id) FROM posts GROUP BY user_id

実はこの場合エラーは発生せず次のような結果を取得できます。

つまり、グループ化したときにエラーが発生するのは次の場合です。

“GROUP BY” で指定していないカラムが “SELECT” に入っている
ただし、MAX() や MIN() などの集計関数を使っていればどのカラムでもOK

なぜエラーになるのか？

ざっくり言うとMySQLが優柔不断で、

「いやいや、僕にはどのデータか決めらんないよ・・・😫」

となっています。

では、実際の例を見てみましょう。
例えば次のようなテーブルです。

見ていただくと分かるとおり、ユーザーID1のデータを赤、2を緑で色分けしています。

では、user_idでグループ化します。
（分かりやすくデータはid、contentだけ）

では、この状態でcontentを取得しようとすると、MySQLはどう思うでしょうか。

「グループ化したのはいいけど、contentはいっぱいあるからどれを取ってくればいいか分からない・・・😥」となります。

これがエラーの全貌です。

MAX()やMIN()を使うとエラーが出なくなるのもこれが理由です。（「どれ？？？」がなくなるからです）

解決するには

では、以下のテーブルで「ユーザーごとの最新記事を取得する」ためにはどうすればいいかを見ていきましょう。

※なお、最後のもの以外は、Laravel 7.xを使ったコードと実際のSQL文の２つをご紹介します。

コードを分ける

まず最もシンプルなのが、コードを分割する方法です。

つまり、

ユーザーごとの最新IDを取得する
そのIDを元にpostsデータを取得する

となります。

// １．ユーザーごとの最新IDを取得
$ids = \App\Post::selectRaw('MAX(id) AS id')
    ->groupBy('user_id')
    ->pluck('id');

// ２．IDを元にデータを取得
$posts = \App\Post::whereIn('id', $ids)
    ->orderBy('id', 'DESC')
    ->get();

実際のSQL文はこうなります。

SELECT MAX(id) AS id FROM posts GROUP BY user_id

SELECT * FROM posts WHERE id IN (（ここに取得したID）) ORDER BY id DESC

WHERE句にサブクエリを使う

WHERE *** IN句の中にサブクエリを作って実装する方法です。

$posts = \App\Post::whereIn('id', function($q) {

    // 👇 サブクエリ
    return $q->from('posts')
        ->selectRaw('MAX(id) as max_id')
        ->groupBy('user_id');

})
->orderBy('id', 'DESC')
->get();

実行されるSQL文はこうなります。

SELECT * FROM posts 
WHERE id IN 
  (
    SELECT MAX(id) AS max_id FROM posts GROUP BY user_id
  )
ORDER BY id DESC

サブクエリで結合する

こちらもサブクエリを使いますが、使う場所はINNER JOINの中です。

$posts = \App\Post::join(
    \DB::raw('(SELECT MAX(id) AS max_id FROM posts GROUP BY user_id) AS latest'),
    'posts.id', '=', 'latest.max_id'
)
->orderBy('id', 'DESC')
->get();

実行されるSQL文はこうなります。

SELECT * FROM posts 
  INNER JOIN
  (
    SELECT MAX(id) AS max_id FROM posts GROUP BY user_id
  )
  AS latest 
  ON posts.id = latest.max_id 
ORDER BY id DESC

only_full_group_byを解除する

MySQLが厳格なSQLを要求しないようにすることができます。
Laravelの場合、configファイルで設定します。

⚠ ただし、この方法はあまりおすすめしません。行のデータ整合性がくずれる可能性があるからです。（例：idは最新のものなのに、contentは別の行から取得されてしまう）

/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' => false, // 👈 ここを "false" にする
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],