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

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

環境

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

問題

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

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

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

環境

前提

問題

うまくいかない例

DISTINCT と ORDER BY

DISTINCT ON

正解

サブクエリを使う

最後に

`DISTINCT` と `ORDER BY`

`DISTINCT ON`