自社で開発しているRailsアプリでGROUP BYとORDER BYを組み合わせて使いたい場合があったので調べたことを共有したいと思います。
GROUP BYとORDER BYを併用する際の注意点
そもそもGROUP BYとORDER BYを普通に1つのクエリで実行すると思った結果がでません。
例えば以下のようなテーブルからauthor_idごとに一番古い記事のデータを取得するとします。
mysql> select * from entries;
+----+-------+------+-----------+---------------------+
| id | title | body | author_id | created_at |
+----+-------+------+-----------+---------------------+
| 1 | text | text | 1 | 2018-01-30 21:05:25 |
| 2 | text | text | 2 | 2018-01-30 21:05:35 |
| 3 | text | text | 2 | 2018-01-31 21:05:38 |
| 4 | text | text | 1 | 2018-01-31 21:05:45 |
+----+-------+------+-----------+---------------------+
4 rows in set (0.00 sec)GROUP BYとORDER BYを使ってみると思ったような結果が返ってきません。
mysql> select * from entries group by author_id order by created_at desc;
+----+-------+------+-----------+---------------------+
| id | title | body | author_id | created_at |
+----+-------+------+-----------+---------------------+
| 2 | text | text | 2 | 2018-01-30 21:05:35 |
| 1 | text | text | 1 | 2018-01-30 21:05:25 |
+----+-------+------+-----------+---------------------+
2 rows in set (0.00 sec)Googleで調べたところGROUP BYとORDER BYを併用すると、先にGROUP BYが実行されるため意図した結果とならないようです。
参考：MySQLでGROUP BYとORDER BYを同時に使用する場合に気をつけたいこと | 日記の間 | あかつきのお宿
サブクエリでORDER BYを優先実行
ではどうするかというとサブクエリを使います。
参考：[SQL] 7. サブクエリ 1 | TECHSCORE(テックスコア)
mysql> select * from (select * from entries order by created_at desc) A group by author_id;
+----+-------+------+-----------+---------------------+
| id | title | body | author_id | created_at |
+----+-------+------+-----------+---------------------+
| 4 | text | text | 1 | 2018-01-31 21:05:45 |
| 3 | text | text | 2 | 2018-01-31 21:05:38 |
+----+-------+------+-----------+---------------------+
2 rows in set (0.01 sec)期待通りの結果になりました。
Railsでの実現方法
ではここからが本題でRailsでどうやって表現するか。
最初に思いついたのはfind_by_sql で直にSQLを書く方法です。
ただ、これだとActiveRecordの便利な機能が全く使えなくなる（includes など）のでイマイチでした。
そこで色々と調べる内にActiveRecord::QueryMethods にfrom というメソッドがあるということがわかりました。
参考：http://api.rubyonrails.org/v4.2.10/classes/ActiveRecord/QueryMethods.html#method-i-from
今回の例で言うと以下のような形になるかと思います。
require 'active_record'
require 'pp'
config = {
adapter: 'mysql2',
host: 'localhost',
database: 'sample',
port: 3306,
username: 'root',
password: '',
encoding: 'utf8',
timeout: 5000,
}
ActiveRecord::Base.establish_connection(config)
class Entry < ActiveRecord::Base
end
entries = Entry.order(created_at: :desc)
pp Entry.group(:author_id).from(entries, :entries)
=>
[#<Entry:0x00007fda0417e3d8
id: 4,
title: "text",
body: "text",
author_id: 1,
created_at: 2018-01-31 21:05:45 UTC>,
#<Entry:0x00007fda04175120
id: 3,
title: "text",
body: "text",
author_id: 2,
created_at: 2018-01-31 21:05:38 UTC>]先に、
entries = Entry.order(created_at: :desc)の部分でORDER BYによる整列をしてから、
Entry.group(:author_id).from(entries, :entries)でGROUP BYするという流れになります。
これで思った通りの処理ができるようになりました。
APPENDIX
速度的な所はどうなのだろうと気になって調べてみる以下のような記事を見つけました。
参考：MySQL のサブクエリって、ほんとに遅いの？ ｜ Developers.IO
どうやらMySQLのバージョンが5.6以上であれば、それなりの速度が出るようです。
今回必要になった箇所については、そこまでのデータ件数ではなかったので、とりあえずはこの方法で行く予定ですが、パフォーマンスによっては別の方法を考える必要がでてきそうです。
まとめ
Railsって本当に便利ですよね！皆さんもぜひ上手にRailsを乗りこなして下さい。