【Laravel】join, group by, count(*)したいとき

やりたいこと

・グループごとの集計人数を出したい
・SQLはかけるがLaravelのクエリビルダの使い方がわからない

Laravelでなかなかうまくいかなかったので学びを記事にしました。

テーブル構造

例えば以下のようなテーブルがある時
↓（親）groupsテーブル

#	name
1	group1
2	group2
3	group3
4	group4
5	group5
6	group6

↓（子）usersテーブル

#	name	group_id
1	user1	1
2	user2	1
3	user3	2
4	user4	2
5	user5	3
6	user6	4

SQL文

「group」ごとに何人の「user」が所属しているかを集計したいとき。
素のSQL文では以下のようになります。

select
  g.id, g.name, count(u.group_id)
from
  groups g
left outer join users u
on g.id = u.group_id
group by u.group_id;

クエリビルダ

Laravelで表すと以下になります。

$groups = DB::table('groups')  //＄groupsは変数にいれてるだけ
             ->leftJoin('users', 'groups.id', '=', 'users.group_id')
             ->select('groups.id', 'groups.name', DB::raw("count(users.group_id) as count"))
             ->groupBy('groups.id')
             ->get();

・外部結合は　leftjoin()（内部結合してしまうと誰も所属してないグループが無視される）
・グループ化はgroupBy()
・集計関数は素のSQL文で書く必要あるためDB::raw()を使います
（私は集計関数に時間かかりました）

groupByがうまく動かない問題

私の場合は以下のようなエラーが出ました。

SQLSTATE[42000]: Syntax error or access violation: 1055 '（アプリ名.groups.name' isn't in GROUP BY

これが書き方なのか何のエラーかわからず解決に時間かかってしましました。

実は Laravelのバージョンによってはうまく動作しません。
ちなみに私はLaravel8です。

“strict”モードをfalseは本質的な解決ではない

コメントでご指定をいただき訂正します。
以前は下記のようなやり方で解決したと思っておりました。

“strict”モードをfalseにし本質的な解決ではない以前のやり方

結論いうと
config/database.phpの “strict”モードをfalseにすることでエラーが表示されなくなります。

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', 'laravel-blog'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => false,//←この部分をtrueからfalseへ
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

これでうまく表示されるようになります！！
MySQLのセキュリティみたいなものがデフォルトで厳しく？なってるみたいで
グループ化による重複するレコードが問題みたい・・・です(^_^;)
この問題を最初に解決した人スゴイ(*_*)

実際のエラーの内容は
「selectで設定したカラムを全てgroup byにせよ」
ということが再度調べたら判明しました！
このことを私は理解できてませんでした（＠＠）

ご指摘ありがとうございます！

$groups = DB::table('groups')  //＄groupsは変数にいれてるだけ
             ->leftJoin('users', 'groups.id', '=', 'users.group_id')
             ->select('groups.id', 'groups.name', DB::raw("count(users.group_id) as count"))
             ->groupBy('groups.id')
             ->groupBy('groups.name') //←こちらを追加
             ->get();

これで“strict”モードをfalseにすることなく正常に動作しました＾＾

結果

先ほどの$groupをforeachで取り出すと

groups.id	groups.name	count
1	group1	2
2	group2	2
3	group3	1
4	group4	1
5	group5	0
6	group6	0

こんな感じになります (^-^)
これでビューへ渡す変数が一つで済むし表示が簡単になります！