【Laraval】Builder@groupBy()->count() しても期待通りにカウントされない理由

環境

$ sail php -v
PHP 8.2.13 (cli) (built: Nov 24 2023 08:47:18) (NTS)

$ sail artisan --version
Laravel Framework 10.39.0

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

サンプルコード

• https://github.com/orange-alien/laravel-blog/pull/2

課題

• Illuminate/Database/Query/Builder クラスのインスタンスに対して groupBy()->count() を呼び出すと、期待通りにカウントされない。

例

• 各 user は post をN件(0件以上)投稿できる
• 1件以上の post を投稿した user の人数をカウントしたい

テーブル

users テーブル

mysql> desc users;
+-------------------+-----------------+------+-----+---------+----------------+
| Field             | Type            | Null | Key | Default | Extra          |
+-------------------+-----------------+------+-----+---------+----------------+
| id                | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| name              | varchar(255)    | NO   |     | NULL    |                |
| email             | varchar(255)    | NO   | UNI | NULL    |                |
| email_verified_at | timestamp       | YES  |     | NULL    |                |
| password          | varchar(255)    | NO   |     | NULL    |                |
| remember_token    | varchar(100)    | YES  |     | NULL    |                |
| created_at        | timestamp       | YES  |     | NULL    |                |
| updated_at        | timestamp       | YES  |     | NULL    |                |
+-------------------+-----------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

posts テーブル

mysql> desc posts;
+------------+-----------------+------+-----+---------+----------------+
| Field      | Type            | Null | Key | Default | Extra          |
+------------+-----------------+------+-----+---------+----------------+
| id         | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | bigint unsigned | NO   |     | NULL    |                |
| title      | varchar(255)    | NO   |     | NULL    |                |
| body       | text            | NO   |     | NULL    |                |
| created_at | timestamp       | YES  |     | NULL    |                |
| updated_at | timestamp       | YES  |     | NULL    |                |
+------------+-----------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

クエリビルダ

// カウント(期待した結果にならない)
// SQL : "select `users`.`id`, COUNT(*) as count from `users` inner join `posts` on `users`.`id` = `posts`.`user_id` group by `users`.`id`" 
// 出力 : 1 ... 7 になって欲しい
$query = DB::table('users')->select(
    'users.id',
    DB::raw('COUNT(*) as count'),
)
->join('posts', 'users.id', '=', 'posts.user_id')
->groupBy('users.id');
dump($query->toRawSql());
dump($query->count());

SQL の実行結果

• 7行出力される(期待通り) ... この 7 が 上記の $query->count() の出力になって欲しい

mysql> select `users`.`id`, COUNT(*) as count from `users` inner join `posts` on `users`.`id` = `posts`.`user_id` group by `users`.`id`;
+----+-------+
| id | count |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  4 |     1 |
|  5 |     2 |
|  7 |     1 |
|  8 |     2 |
| 10 |     1 |
+----+-------+
7 rows in set (0.00 sec)

groupBy()->count() が期待通りの結果にならない理由

• Illuminate/Database/Query/Builder のインスタンスの集計用の関数( count、max、min、avg、sum )を実行すると、同クラスの aggregate() メソッドが実行される
• 集計のSQLを実行後、その結果の先頭行(0行目)の値を取得する仕様のため
  • おそらく、「GROUP BY で 集計した結果の行数(今回の例だと7)を取得する」という使い方は想定されていない
    • そういう意味だと、そもそもの期待が間違っている

aggregate

Illuminate/Database/Query/Builder@aggregate

    /**
     * Execute an aggregate function on the database.
     *
     * @param  string  $function
     * @param  array  $columns
     * @return mixed
     */
    public function aggregate($function, $columns = ['*'])
    {
        $results = $this->cloneWithout($this->unions || $this->havings ? [] : ['columns'])
                        ->cloneWithoutBindings($this->unions || $this->havings ? [] : ['select'])
                        ->setAggregate($function, $columns)
                        ->get($columns);

        if (! $results->isEmpty()) {
            return array_change_key_case((array) $results[0])['aggregate'];
        }
    }

上記の results の出力例

• 先頭行(0行目)の値( = 1)が count() メソッドの返り値となる
  • 他の集計関数も同様

[
  {
    "aggregate": 1
  },
  {
    "aggregate": 2
  },
  {
    "aggregate": 1
  },
  {
    "aggregate": 2
  },
  {
    "aggregate": 1
  },
  {
    "aggregate": 2
  },
  {
    "aggregate": 1
  }
]

【参考】SQL の実行結果(先述のものと同じ)

• 上記の結果(各 id 毎の count の値)は以下と同じであることを確認できる

mysql> select `users`.`id`, COUNT(*) as count from `users` inner join `posts` on `users`.`id` = `posts`.`user_id` group by `users`.`id`;
+----+-------+
| id | count |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  4 |     1 |
|  5 |     2 |
|  7 |     1 |
|  8 |     2 |
| 10 |     1 |
+----+-------+
7 rows in set (0.00 sec)

解決方法

• groupBy()->count() を使わない
  • 先述の通り、GROUP BY した結果での集計は想定されていない仕様である(今回の期待自体が誤りである)ため
    • 他の集計関数(max、min、avg、sum)も同様
• 他の方法で代替する
  • 例1 : groupBy() の結果をサブクエリとして count() する
  • 例2 : 今回の例(postを1件以上投稿したuserの人数をカウント)においては、SELECT DISTINCT でもカウント可能

例1 : GROUP BY したクエリをサブクエリとして、カウントする

// カウント(期待通りの結果になる)
// SQL : select * from (select `users`.`id`, COUNT(*) as count from `users` inner join `posts` on `users`.`id` = `posts`.`user_id` group by `users`.`id`) as `sub`
// 出力 : 7
$query2 = DB::query()->fromSub($query, 'sub');
dump($query2->toRawSql());
dump($query2->count());

例2 : SELECT DISTINCT を用いる(GROUP BY しない)

// カウント(期待通りの結果になる)
// SQL : select distinct * from `posts`
// 出力 : 7
$query2 = DB::table('posts')->distinct('user_id');
dump($query2->toRawSql());
dd($query2->count());

参考

• laravel/framework
  • Illuminate/Database/Query/Builder@aggregate