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
この場合だと商品ごとに纏められた後、日付での並び替えが発生するため古い発注日のレコードが取得されてしまった。
最終的な形
@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 ...)