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

10671 ワード
SQL Laravel SQL テキストリンク

やりたいこと

・グループごとの集計人数を出したい
・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

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

Codeforces Round #267 (Div. 2)