【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](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)
```


### クエリビルダ

```php
// カウント(期待した結果にならない)
// 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
```php: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() メソッドの返り値となる
    * 他の集計関数も同様

```javascript
[
  {
    "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 したクエリをサブクエリとして、カウントする
```php
// カウント(期待通りの結果になる)
// 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 しない)

```php
// カウント(期待通りの結果になる)
// 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&#64;aggregate](https://github.com/laravel/framework/blob/10.x/src/Illuminate/Database/Query/Builder.php#L3216)