環境
- Ruby: 3.0.2
- Rails: 6.1.4.1
- MySQL: 5.7
※ Railsのバージョンを上げたりDBMSを変えたりしたときには再現しなかった問題なので、環境依存している話かもしれません。
やりたいこと
あるモデルのレコードを、has_many の関係にあるモデルのうち最新のレコードでorderした上で、limitして件数を絞り込みたいです。
具体例として、チャット管理アプリを作成しているとします。
このアプリでは以下のようなモデルが存在します。
class ChatRoom < ApplicationRecord
has_many :chat_messages
end
class ChatMessage < ApplicationRecord
belongs_to :chat_room
end
ここで、チャットメッセージの最新の投稿日時の順番にチャットルームを並び替えて、先頭の10件だけ取得したい、というのが今回の話です。
データは以下のようなものが存在するとします。
chat_rooms
id | name |
---|---|
1 | ルーム1 |
2 | ルーム2 |
3 | ルーム3 |
4 | ルーム4 |
5 | ルーム5 |
6 | ルーム6 |
7 | ルーム7 |
8 | ルーム8 |
9 | ルーム9 |
10 | ルーム10 |
11 | ルーム11 |
chat_messages
id | chat_room_id | created_at |
---|---|---|
1 | 1 | 2022-08-19 20:00:00 |
2 | 2 | 2022-08-19 19:00:00 |
3 | 3 | 2022-08-19 18:00:00 |
4 | 4 | 2022-08-19 17:00:00 |
5 | 5 | 2022-08-19 16:00:00 |
6 | 6 | 2022-08-19 15:00:00 |
7 | 7 | 2022-08-19 14:00:00 |
8 | 8 | 2022-08-19 13:00:00 |
9 | 8 | 2022-08-19 12:00:00 |
10 | 8 | 2022-08-19 11:00:00 |
11 | 9 | 2022-08-19 10:00:00 |
12 | 10 | 2022-08-19 09:00:00 |
13 | 11 | 2022-08-19 08:00:00 |
ルーム1〜10が順番に取得できると嬉しいですね。
失敗例
以下のようにして取得しようとすると、ルーム1〜8までしか取れません。
class ChatRoomsController < ApplicationController
def index
@chat_rooms = ChatRoom.includes(:chat_messages)
.order('chat_messages.created_at DESC')
.limit(10)
end
end
実際に発行されているSQLを確認してみます。
SQL (1.1ms) SELECT DISTINCT chat_messages.created_at AS alias_0, `chat_rooms`.`id` FROM `chat_rooms` LEFT OUTER JOIN `chat_messages` ON `chat_messages`.`chat_room_id` = `chat_rooms`.`id` ORDER BY chat_messages.created_at DESC LIMIT 10
↳ app/views/chat_rooms/index.html.erb:15
SQL (1.3ms) SELECT `chat_rooms`.`id` AS t0_r0, `chat_rooms`.`name` AS t0_r1, `chat_rooms`.`created_at` AS t0_r2, `chat_rooms`.`updated_at` AS t0_r3, `chat_messages`.`id` AS t1_r0, `chat_messages`.`content` AS t1_r1, `chat_messages`.`chat_room_id` AS t1_r2, `chat_messages`.`created_at` AS t1_r3, `chat_messages`.`updated_at` AS t1_r4 FROM `chat_rooms` LEFT OUTER JOIN `chat_messages` ON `chat_messages`.`chat_room_id` = `chat_rooms`.`id` WHERE `chat_rooms`.`id` IN (1, 2, 3, 4, 5, 6, 7, 8, 8, 8) ORDER BY chat_messages.created_at DESC
ここで注目したいのは LIMIT 10
がどこに掛かっているかです。
10件に絞り込まれているのは、 chat_rooms
ではなく chat_rooms
と chat_messages
をLEFT OUTER JOINした結果です。
つまり、以下のようなJOIN結果に対して、上から10件取得し、その10件の chat_rooms.id
を使って chat_rooms
をSELECTしています。
chat_rooms.id | chat_messages.id | chat_messages.created_at |
---|---|---|
1 | 1 | 2022-08-19 20:00:00 |
2 | 2 | 2022-08-19 19:00:00 |
3 | 3 | 2022-08-19 18:00:00 |
4 | 4 | 2022-08-19 17:00:00 |
5 | 5 | 2022-08-19 16:00:00 |
6 | 6 | 2022-08-19 15:00:00 |
7 | 7 | 2022-08-19 14:00:00 |
8 | 8 | 2022-08-19 13:00:00 |
8 | 9 | 2022-08-19 12:00:00 |
8 | 10 | 2022-08-19 11:00:00 |
9 | 11 | 2022-08-19 10:00:00 |
10 | 12 | 2022-08-19 09:00:00 |
11 | 13 | 2022-08-19 08:00:00 |
なので、 chat_rooms.id
が1〜8のものしかSELECTされないわけですね。
ちなみに、kaminariの page
/ per
メソッドを使っても同じことが起こります。
というより、kaminariを使っていてこの問題に気づいたのでした。
この記事では説明のため、簡略化して limit
を使うようにしています。
対処法
思いつくものが2つあるので順に紹介していきます。
SQLで捌く
これが一番よさそうです。
やり方は色々あると思うのですが、ここでは極値関数を使って chat_messages.created_at
を比較することにします。
class ChatRoom < ApplicationRecord
has_many :chat_messages
scope :order_by_latest_message, lambda {
CONDITION = <<~SQL
chat_messages.created_at >= (SELECT MAX(created_at)
FROM chat_messages AS latest_chat_messages
WHERE chat_messages.chat_room_id = latest_chat_messages.chat_room_id
GROUP BY chat_room_id)
SQL
joins(:chat_messages).where(CONDITION).order('chat_messages.created_at DESC')
}
end
class ChatRoomsController < ApplicationController
def index
@chat_rooms = ChatRoom.order_by_latest_message.limit(10)
end
end
Array化する
10件limitする際にJOIN結果を絞り込もうとするからおかしくなるのであって、ChatRoomをArrayにしてから10件絞り込めば問題は解決します。
class ChatRoomsController < ApplicationController
def index
@chat_rooms = ChatRoom.includes(:chat_messages)
.order('chat_messages.created_at DESC')
.to_a
.take(10)
end
end
ただしこの場合、取得したレコードを全てArrayにするので、件数によっては処理が重くなります。