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

```rb:order.rb
class Order < ApplicationRecord
  has_many :order_details
  has_many :products, through: :order_details
end
```

```rb:order_detail.rb
class OrderDetail < ApplicationRecord
  belongs_to :order
  belongs_to :product
end
```

```rb: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` 句が使えます. したがって

```rb
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` 句が使えるので

```rb
OrderDetail.
  select("DISTINCT ON (product_id) product_id").
  order(created_at: :desc)
```

と書いてみましょう. すると

```
ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions
```

と怒られます. そう言われるならばということで

```rb
OrderDetail.
  select("DISTINCT ON (product_id) product_id").
  order(:product_id, created_at: :desc)
```

と書けば, ようやく怒られなくなります. しかし

```rb
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` でソートしてあげれば上手くいきます:

```rb
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` メソッドに配列を渡せば, その順番どおりに結果が返ってきます:

```rb
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 での方法は他の記事でいくつか紹介されていますので, ここでは割愛します.
例えば

  - [指定したidの順序でデータを取得する](https://qiita.com/takuya-i/items/a7ef5105952e32f1affd)
  - [[Rails]ActiveRecordで、whereで指定した順番通りに結果をソートしたい時どうするか](http://y-yagi.tumblr.com/post/126544857465/railsactiverecord%E3%81%A7where%E3%81%A7%E6%8C%87%E5%AE%9A%E3%81%97%E3%81%9F%E9%A0%86%E7%95%AA%E9%80%9A%E3%82%8A%E3%81%AB%E7%B5%90%E6%9E%9C%E3%82%92%E3%82%BD%E3%83%BC%E3%83%88%E3%81%97%E3%81%9F%E3%81%84%E6%99%82%E3%81%A9%E3%81%86%E3%81%99%E3%82%8B%E3%81%8B)

といった記事があります.
あるいは `products INNER JOIN order_details` しても良いかもしれません:

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