【ActiveRecord】指定のレコードを抽出する方法【実装方法】

Question

現状・実現したいこと

標記の件について質問させていただきます。
【実現したいこと】

1. competition_idが同じ番号のものをまとめる
2. そのcompetition_idでグルーピングされたもののなかで、各pointsカラムが最高値のもののレコードを取得

(各competitionの1位を取得したい)

【現状】
現在、DBにはitemsテーブルとcompetitionsテーブルがあります。
カラムや格納されているデータについては以下の通りです。

○itemsテーブルには、そのitemが持つpointsがある。
○itemsテーブルには、どのcompetitionに属しているかを指定するcompetiton_idが存在している。

試したこと

Item.maximum("points")
最大値が出力されただけで、レコードの出力をしたい

Item.order("points DESC")
グルーピングされていなく、pointsの降順で取得された

Item.order("points DESC").group(:competition_id) 
pointsの最高値のレコード（希望している結果）ではなく、competition_idの初めのレコードを取得された

いろいろと試行錯誤してみましたが、希望通りの抽出ができません。
上記のレコードを抽出するActiveRecordをご教授いただければ幸いです。

補足情報（FW/ツールのバージョンなど）

• ruby 2.7.0p0
• Rails 6.1.0
• sqlite

Comments

[m.ts10806]

まずはSQLで組んでみましたか？

[katahik]

m.ts10806 様
ありがとうございます。

SQLは以下の通り、組みました。

query = "select * from items where id in (select max(points) from items group by competition_id) order by id desc;"

しかし、この結果は

[#<Item id: 43, name: "food9", image: "food_sample.jpeg", points: 98, competition_id: 5, created_at: "2020-12-31 22:51:17.966582000 +0000", updated_at: "2020-12-31 22:51:17.966582000 +0000">]

このように、希望したものでないものが抽出されてしまいます。

Answer 1

下記の通り、自己解決しました

query = "SELECT *,MAX(points) FROM Items GROUP BY competition_id"

@winners = Item.find_by_sql(query)