Edited at

SELECT DISTINCT したカラムと別のカラムで ORDER BY する

そのものズバリの情報が見つけられず解決に時間がかかったので, 備忘録として残しておきます.


環境

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 の関係があるでしょう:


order.rb

class Order < ApplicationRecord

has_many :order_details
has_many :products, through: :order_details
end


order_detail.rb

class OrderDetail < ApplicationRecord

belongs_to :order
belongs_to :product
end


product.rb

class Product < ApplicationRecord

end


問題

各商品を, 最近購入された順にソートして取得したくなったとします. どんなコードを書けば良いでしょうか.

(簡単のため OrderDetailOrder と同時刻に作られるとします.)

つまり, 以下のような 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


うまくいかない例


DISTINCTORDER 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 に依存してしまっているあたりとか...)