Rails でカテゴリーごとに人気なものを返すクエリを教えて下さい

前提

Rails の習作に映画のレビューサービスを作っています。
Rails 6.0, MySQL 5.7 で sql_mode=only_full_group_by が有効な環境です。

実現したいこと

Railsで category_id, film_id, score の列を持つテーブル、 cateogory_film_scores で group by をしたいです。
category_id, film_id は unique制約があります。

ユーザが複数の category_id を指定するので、その category_id で重複なく、 score が高い順かつ、 film_id がかぶらないように返したいです。
sql_mode=TRADITONAL であれば

ruby
1CategoryFilmScore
2  .where(category_id: user_selected_cateogry_id)
3  .group(:film_id)
4  .order(score: :desc)
5  .limit(10)

というクエリで書けるものでした。

例をあげると, 以下の表でユーザがcategory_id = [10, 12] を選択した場合に

id	category_id	film_id	score
1	10	100	10
2	10	101	3
3	10	102	9
4	11	100	4
5	11	101	4
6	11	102	5
7	12	100	10
8	12	101	7
9	12	102	5

次の結果を得たいです。

id	category_id	film_id	score
1	10	100	10
8	12	101	7

Rails でどういうコードを書けばいいでしょうか？
また、どういった index を貼るべきでしょうか？

yuma.inaura

2022/12/01 13:00

score が最も高いものではなく、何か別の結果が得たいんでしょうか？ category_id film_id score 10 100 5 は元々のデータに存在しないですよね

heroherotail

2022/12/01 13:11 編集

失礼いたしました。こちら間違えていたので修正しました。 https://teratail.com/questions/history-questions/vbudr2rnbei9lw score が最も高いものを抽出したいです。

行動規範の内容に同意します

回答1件

:category_id でもグループ化するといかがですか？

ruby
1CategoryFilmScore
2  .where(category_id: user_selected_cateogry_id)
3  .group(:category_id, :film_id)
4  .order(score: :desc)
5  .limit(10)

投稿2022/12/02 09:52

yuma.inaura

総合スコア1453

heroherotail

2022/12/02 10:31

`.where` や `.limit` は本質的な問題ではなさそうなので一旦省いて↓を `.to_sql` してみました。 ```ruby CategoryFilmScore .group(:category_id, :film_id) .order(score: :desc) ``` ```sql SELECT `category_film_scores`.* FROM `category_film_scores` GROUP BY `category_film_scores`.`film_id`, `category_film_scores`.`category_id` ORDER BY `category_film_scores`.`score` DESC ``` やはり max などの集約関数がないため > which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (Mysql2::Error) というエラーが発生します。