そのものズバリの情報が見つけられず解決に時間がかかったので, 備忘録として残しておきます.
環境
Ruby 2.5.1
Rails 5.2.1
PostgreSQL 9.5.14
pg 1.1.3
前提
例として, 商品を表す Product モデル, 注文を表す Order モデルがあるとします.
1 回の注文で複数の商品を購入したいでしょうから, 何らかの中間テーブル (OrderDetail モデルとしましょう) を介して
Order has many Products through OrderDetails の関係があるでしょう:
class Order < ApplicationRecord
has_many :order_details
has_many :products, through: :order_details
end
class OrderDetail < ApplicationRecord
belongs_to :order
belongs_to :product
end
class Product < ApplicationRecord
end
問題
各商品を, 最近購入された順にソートして取得したくなったとします. どんなコードを書けば良いでしょうか.
(簡単のため OrderDetail は Order と同時刻に作られるとします.)
つまり, 以下のような order_details があったとき
| order_id | product_id | created_at |
|---|---|---|
| 1 | 1 | 2018-01-01 |
| 2 | 2 | 2018-06-30 |
| 2 | 3 | 2018-06-30 |
| 3 | 2 | 2018-12-31 |
以下のような順番で product_id が取得できると嬉しい, ということです:
| product_id | (created_at) |
|---|---|
| 2 | 2018-12-31 |
| 3 | 2018-06-30 |
| 1 | 2018-01-01 |
うまくいかない例
DISTINCT と ORDER BY
重複の削除には DISTINCT 句が使えます. またソートには ORDER BY 句が使えます. したがって
OrderDetail.
select(:product_id).
distinct.
order(created_at: :desc)
と書けば良いように思えますが, こうすると
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
と怒られてしまいます. つまり SELECT DISTINCT するのであれば, 出力の並び順を保証するため, ORDER BY するカラムは SELECT の中に含まれなければならない, ということです.
しかし今回は重複した product_id のうち一番最近購入されたものを取ってきたいので, created_at まで含めた組では SELECT DISTINCT したくありません.
DISTINCT ON
PostgreSQL では DISTINCT ON 句が使えるので
OrderDetail.
select("DISTINCT ON (product_id) product_id").
order(created_at: :desc)
と書いてみましょう. すると
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
と怒られます. そう言われるならばということで
OrderDetail.
select("DISTINCT ON (product_id) product_id").
order(:product_id, created_at: :desc)
と書けば, ようやく怒られなくなります. しかし
OrderDetail.
select("DISTINCT ON (product_id) *").
order(:product_id, created_at: :desc).
each{ |od| puts "#{od.product_id} #{od.created_at}" }
を試してみると
1 2018-01-01
2 2018-12-31
3 2018-06-30
と返ってきます. つまり取得したいデータの集合自体は正しいのですが, order メソッドの最初の引数である :product_id に対するソートが一番に効いてしまっているのです.
これでは当初の「最近購入された順に商品をソートしたい」という目的が達成できません.
正解
サブクエリを使う
前項の DISTINCT ON を使った例でデータの集合自体は正しく取得できるので, そのクエリをサブクエリとしてさらに created_at でソートしてあげれば上手くいきます:
OrderDetail.
select(:product_id).
where(
id: OrderDetail.
select("DISTINCT ON (product_id) id").
order(:product_id, created_at: :desc)
).
order(created_at: :desc)
これで当初の目的順に product_id が取得できるので, あとはその順番通りに結果を受け取れば OK です.
Rails 5 では find メソッドに配列を渡せば, その順番どおりに結果が返ってきます:
product_ids = OrderDetail.
where(
id: OrderDetail.
select("DISTINCT ON (product_id) id").
order(:product_id, created_at: :desc)
).
order(created_at: :desc).
pluck(:product_id)
Product.find(product_ids)
Rails 4 での方法は他の記事でいくつか紹介されていますので, ここでは割愛します.
例えば
といった記事があります.
あるいは products INNER JOIN order_details しても良いかもしれません:
order_detail_ids = OrderDetail.
select("DISTINCT ON (product_id) id").
order(:product_id, created_at: :desc)
Product.
joins(
"INNER JOIN order_details ON order_details.product_id = products.id"
).
where("order_details.id": order_detail_ids).
order("order_details.created_at DESC")
最後に
この記事の内容に誤りを発見された方, もっと良いやり方をご存知の方は教えていただけると嬉しいです. (PostgreSQL に依存してしまっているあたりとか...)