arait-code’s RC

もうすぐエンジニア転職して2年になります。

rails mysqlでgroupとorderを同時にしたい時

環境

ruby 3.0.3p157

gem 'rails', '~> 6.1.0'

gem 'mysql2', '>= 0.3.18'

やりたかったこと

発注データから商品ごとに最新の発注で使われた発注先を取り出す

結論

MIN,MAXを絡めてselectで発注日.MAXとしつつ、orderで指定、groupで纏める、とすることで取得できた

テーブル構成

店舗テーブル(pharmacy)

id name ...
1 新宿店 ...

発注先マスタ(wholesaler)

id code name
1 1 卸1
2 2 卸2
3 3 卸3

発注テーブル(OrderProduct)

id 店舗ID 商品ID 発注状態 発注日 発注先ID
1 1 ロキソニン ordered 2021-01-01 1
2 1 ロキソニン ordered 2021-01-02 2
3 1 バファリン ordered 2021-01-01 1

商品マスター(master_product)

id JANコード 商品名 ...
1 0000000000001 ロキソニン ...
2 0000000000002 バファリン ...

失敗ケース:

普通にorderとgroupをメソッドチェーンで繋ぐ

@last_wholesaler_id = OrderProduct
                        .where(pharmacy_id: @pharmacy.id,
                               発注状態: 'ordered',
                               master_product_id: test)
                        .order(order_appointed_on: :desc))
                        .group(:master_product_id)

サブクエリでやる

@last_wholesaler_id = OrderProduct
                        .from(OrderProduct
                                .where(pharmacy_id: @pharmacy.id,
                                       発注状態: 'ordered',
                                       master_product_id: test)
                                .order(order_appointed_on: :desc))
                        .group(:master_product_id)

これらだと意図した結果は得られなかった。

id 店舗ID 商品ID 発注状態 発注日 発注先ID
1 1 ロキソニン ordered 2021-01-01 1
3 1 バファリン ordered 2021-01-01 1

なぜ?

ORDER BYはGROUP BYの後で処理されるため

FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

この場合だと商品ごとに纏められた後、日付での並び替えが発生するため古い発注日のレコードが取得されてしまった。

teratail.com

最終的な形

@last_wholesaler_id = OrderProduct
                          .includes(:wholesaler)
                          .select('master_product_id, wholesaler_id, max(order_appointed_on), max(created_at)')
                          .where(pharmacy_id: @pharmacy.id,
                              order_state: 'ordered',
                              master_product_id: products_ids)
                          .order('max(order_appointed_on)')
                          .order('max(created_at)')
                          .group('master_product_id')
id 店舗ID 商品ID 発注状態 発注日 発注先ID
2 1 ロキソニン ordered 2021-01-02 2
3 1 バファリン ordered 2021-01-01 1

ロキソニンの最新日付のレコードが取れているため、これでOKです。

サブクエリでMAXなどを使う形でも取得出来る

SELECT * FROM table WHERE created_at IN(SELECT MAX(created_at) FROM table GROUP BY ...)