おい、orderByとページネーション組み合わせる時は気をつけろよ

とある管理画面でバグが発生

• 書類を一覧表示する機能がある
• 書類は送信日というdate型のカラムを持っている。
• 書類を一覧表示する機能にはページャー機能が実装されている。

そこで、送信日順に並び替えようと、クエリにorderByを追加した。

Document::query()->orderBy('send_date')->paginate(10);

この際にバグが発生。どういうバグが発生したかと言うと、

ページャーを1(1件目~10件目)から2(11件目~20件目)に切り替えた際
ページャーを切り替えるたびに新規でクエリを発行している為、ソートキーに指定したカラムのデータに重複があった場合、以前取得したデータが再度取得されてしまう現象だ。

バグの内容

例えば、以下のようなテーブルが存在しているとする

id	送信日
1	2023年11月1日
2	2023年11月2日
3	2023年11月1日
4	2023年11月1日
5	2023年11月1日
6	2023年11月1日
7	2023年11月1日

日付順かつ1件目から3件目を要求し、以下のようなレコードが取得できたとする。

id	送信日
1	2023年11月1日
3	2023年11月1日
4	2023年11月1日

次に4件目から6件目を要求した際

id	送信日
4	2023年11月1日
5	2023年11月1日
6	2023年11月1日

こういう結果が出るかもしれないという話。

orderBy('send_date')

は、「日付順」である事だけをDBに保証させるのであって、同じ日付同士のレコード並び順は保証してくれないのである。

この例の場合、1件目から3件目を要求した時の
「DBが思う最適な同日付のレコード群の並び順」

と、4件目から6件目までを要求した時の
「DBが思う最適な同日付のレコード群の並び順」
が違っているのである。

具体的に表現すると
日付順で表示した際、11月1日と2日は明確にソートされる、しかし同一日のレコードは発行するクエリ次第でDBが並び順を決めてしまうのだ。

何が起きているか

1. わい「日付ソートした順の1件目から3件目までくれ」
2. DB「ほらよ、idが1と3と4の奴あげるわ」
3. わい「さんがつ」
4. わい「次は4件目から6件目までくれ」
5. DB「ほらよ、idが4と5と6の奴あげるわ」
6. わい「ふぁ！？id4はさっきもらったけど！？」
7. DB「は？日付順っていう縛り以外聞いてへんから同じ日付同士の並び順はわいが勝手に決めるやで、
8. 1件目から3件目まで要求されたと2件目から4件目要求された時で、全体の同一日の並び方変えてんねん」
9. わい「ふぁー、ほなid順っていう縛りも入れてやらんといけへんな」

2の時点のバックグラウンド

DB「まず日付順でソートするでー」

id	送信日
1	2023年11月1日
3	2023年11月1日
4	2023年11月1日
5	2023年11月1日
6	2023年11月1日
7	2023年11月1日
2	2023年11月2日

ほなこっから1件目から3件目を渡すか

id	送信日
1	2023年11月1日
3	2023年11月1日
4	2023年11月1日

5の時点のバックグラウンド

DB「まず日付順でソートするでー」

id	送信日
7	2023年11月1日
1	2023年11月1日
3	2023年11月1日
4	2023年11月1日
5	2023年11月1日
6	2023年11月1日
2	2023年11月2日

ほなこっから4件目から6件目まで渡すか

id	送信日
4	2023年11月1日
5	2023年11月1日
6	2023年11月1日

結論

発行するクエリによって、DBが最適だと思う並び順は変動してしまうので、並び順を保証させたければ一意にな値をorderByに加えよう。