Laravel6からLaravel7へのバージョン更新で起こった問題を深～く追及していったら、私のプログラムのバグの発見とともにLaravelフレームワークの内部の変更の背景を知ることになりました。
groupByを使ったパジネーションの問題
Laravel6.xからLaravel7.xに更新して、動作テストを行っていたらこのようなこと起こりました。
まず、テストケースの作成です。ユーザーの一日の歩行数を記録するとして以下の構造を持つstepsのテーブルを作成します。
user_idは、usersのuser.idとして、date_loggedが対象の日付、そしてstepsが歩行数です。
mysql> describe steps; +-------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+---------+----------------+ | id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | user_id | int(10) unsigned | NO | | NULL | | | date_logged | date | NO | | NULL | | | steps | smallint(5) unsigned | NO | | 0 | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | +-------------+----------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)
factory()を利用してDBテーブルにデータを入れます。こんなデータです。
mysql> select * from steps; +----+---------+-------------+-------+---------------------+---------------------+ | id | user_id | date_logged | steps | created_at | updated_at | +----+---------+-------------+-------+---------------------+---------------------+ | 1 | 4 | 2021-02-20 | 1119 | 2021-02-26 15:53:46 | 2021-02-26 15:53:46 | | 2 | 3 | 2021-02-22 | 3955 | 2021-02-26 15:53:46 | 2021-02-26 15:53:46 | | 3 | 4 | 2021-02-21 | 294 | 2021-02-26 15:53:46 | 2021-02-26 15:53:46 | | 4 | 2 | 2021-02-22 | 1473 | 2021-02-26 15:53:46 | 2021-02-26 15:53:46 | | 5 | 1 | 2021-02-22 | 2858 | 2021-02-26 15:53:46 | 2021-02-26 15:53:46 | | 6 | 2 | 2021-02-21 | 1517 | 2021-02-26 15:53:46 | 2021-02-26 15:53:46 | | 7 | 3 | 2021-02-21 | 2913 | 2021-02-26 15:53:46 | 2021-02-26 15:53:46 | | 8 | 2 | 2021-02-21 | 3653 | 2021-02-26 15:53:46 | 2021-02-26 15:53:46 | | 9 | 1 | 2021-02-24 | 309 | 2021-02-26 15:53:46 | 2021-02-26 15:53:46 | | 10 | 1 | 2021-02-24 | 2985 | 2021-02-26 15:53:46 | 2021-02-26 15:53:46 | +----+---------+-------------+-------+---------------------+---------------------+ 10 rows in set (0.01 sec)
さて、Laravel6.xの環境でtinkerを実行して以下のクエリを実行します。日付でグループ化してそれぞれの日付の総歩数を表示したいわけです。しかも、画面で表示するのでパジネーションを使います。
>>> use App\Step;
>>> $pager = Step::select('date_logged', DB::raw('sum(steps) as steps'), 'date_logged')
->groupBy('steps.date_logged')
->paginate(2)
=> Illuminate\Pagination\LengthAwarePaginator {#4152
+onEachSide: 3,
}
>>> $pager->toArray();
=> [
"current_page" => 1,
"data" => [
[
"date_logged" => "2021-02-20",
"steps" => "1119",
],
[
"date_logged" => "2021-02-21",
"steps" => "8377",
],
],
"first_page_url" => "http://localhost?page=1",
"from" => 1,
"last_page" => 2,
"last_page_url" => "http://localhost?page=2",
"next_page_url" => "http://localhost?page=2",
"path" => "http://localhost",
"per_page" => 2,
"prev_page_url" => null,
"to" => 2,
"total" => 4,
]
実行は問題ないですね。全部（total）で合計４つのレコードがあり、１ページあたり２レコード表示で最初の２つレコードを抽出（data）です。
さて、今度は同じクエリをLaravel 7.xで実行すると、
>>> use App\Step;
>>> $pager = Step::select('date_logged', DB::raw('sum(steps) as steps'), 'date_logged')
->groupBy('steps.date_logged')
->paginate(2)
Illuminate\Database\QueryException with message 'SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'date_logged' (SQL: select count(*) as aggregate from (select `date_logged`, sum(steps) as steps, `date_logged` from `steps` group by `steps`.`date_logged`) as `aggregate_table`)'
とクエリがSQLの実行エラーになっていしまいます。
エラーは初歩的なミスで、クエリが返す項目に重複の項目があるよ、という指摘です。そうですね、select()でdate_loggedが重複しています。
しかし、Laravel 6.xでは問題ありませんでしたね。また、以下のpaginate()をget()に置き換えたクエリの実行はLaravel 7.xでも問題はありません。
>>> Step::select('date_logged', DB::raw('sum(steps) as steps'), 'date_logged')
->groupBy('steps.date_logged')
->get();
=> Illuminate\Database\Eloquent\Collection {#3658
all: [
App\Step {#4220
date_logged: "2021-02-20",
steps: "1119",
},
App\Step {#4219
date_logged: "2021-02-21",
steps: "8377",
},
App\Step {#4065
date_logged: "2021-02-22",
steps: "8286",
},
App\Step {#3337
date_logged: "2021-02-24",
steps: "3294",
},
],
}
エラーの原因は何でしょう？paginate()の関数のコードがLaravel7.xに変わった？
Laravel 6.xに戻って、実行されたクエリを見てみると、
>>> $pager = Step::select('date_logged', DB::raw('sum(steps) as steps'), 'date_logged')
->groupBy('steps.date_logged')
->paginate(2)
=> Illuminate\Pagination\LengthAwarePaginator {#3241
+onEachSide: 3,
}
>>> sql();
=> [
[
"query" => "select count(*) as aggregate from `steps` group by `steps`.`date_logged`",
"bindings" => [],
"time" => 1.37,
],
[
"query" => "select `date_logged`, sum(steps) as steps, `date_logged` from `steps` group by `steps`.`date_logged` limit 2 offset 0",
"bindings" => [],
"time" => 0.82,
],
]
]
Laravel 7.xで重複を修正して再度実行すると、
>>> use App\Step; >>> $pager = Step::select('date_logged', DB::raw('sum(steps) as steps')) ->groupBy('steps.date_logged') ->paginate(2)
=> Illuminate\Pagination\LengthAwarePaginator {#4274
+onEachSide: 3,
}
>>> sql()
=> [
[
"query" => "select count(*) as aggregate from (select `date_logged`, sum(steps) as steps from `steps` group by `steps`.`date_logged`) as `aggregate_table`",
"bindings" => [],
"time" => 164.63,
],
[
"query" => "select `date_logged`, sum(steps) as steps from `steps` group by `steps`.`date_logged` limit 2 offset 0",
"bindings" => [],
"time" => 0.42,
],
]
どちらを見てもわかるように、パジネーションを作成するには、指定したレコードを取得するクエリを実行するだけなく、対象となる総レコード数のクエリも実行されます。もちろんそうでないと、全部でのページ数がわかりません。
しかし、そのレコード数計算のためのクエリを比較してみると、Laravel 7.xでは対象のクエリをサブクエリとしてSQL文のFromに与えています。そして、そこの部分で項目の重複を許さないためにエラーとなった次第です。なるほど！
また、Laravel 7.xが一発でレコード数の結果を返しますが、Laravel 6.xのレコード数計算のクエリは結果が複数のレコードで返されますので、さらにそれ自体をカウントしなければいけません。多分にプログラムでカウントしていたのかもしれませんが、効率は良くないですね。
調べてみると
まず、Laravel 6.xのマニュアルでは、この部分
Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually.
現在groupBy文を使用したパジネーションの操作は、Laravelで効率よく実行できません。groupByを使用したパジネーションを使用する必要がある場合はデータベースクエリを実行し、その結果を元にパジネーターを自前で作成してください）
とあります。
この記述はLaravel 7.xのマニュアルにもありますが、最新の7xのコードでは修正されているはずです（次を読むとわかります）。Laravel 8.xではこの記述はありません。
もう少し調べてみると、github.comのissuesにまさにそのマージありました。
[7.x] Run pagination count as subquery for group by and havings
Paginating queries with groupBy or having statements is a long-standing issue in Laravel going back to the very beginning of the framework with literal dozens of raised issues:
#1892, #2761, #3105, #4306, #6985, #7372, #9567, #10632, #14123, #16320, #17406, #22883, #28931
This solution was suggested @acasar years ago but I wrote it off at the time – but honestly I think it’s a lot better than what we have now so I’m bringing it up again for consideration.
groupByあるいはhavingを使用したパジネーションは、フレームワークの開発当初からたくさん問題として取り上げられたLaravelの長年の課題でした。
今回の@acasarが何年も前に提示した解決方法は、当時私が取り上げなかったものですが、正直言って現在使用されているものより良いので、ここで再度取り入れることにします。
なるほど、Laravelの作者でもこういうことがあるのですね。というかたくさんの人が改良のアイデア（あるいは不平）をいつも与えて続けているのは好まれている証拠です。