MySQLなどのRDBで特定カラムをグルーピングしながら、そのカラムが最大値となるレコードのみを取り出したい時があります。
具体的にはコンテンツを最新の閲覧順に並べたい場合がそれに当たりますが、これを実現するには少しクエリを工夫する必要があります。
何年か前にRailsで開発中に同様のユースケースに直面した際、当時自分のSQL力が低いゆえに2度クエリを発行していましたが、今思うとActiveRecordを使っても1クエリで実現できるので、その場合の対応法を紹介します。
ケーススタディ
ユーザーが映画を閲覧した時に履歴が保存される場合、映画を閲覧日順に重複なしで並べるクエリを書いてみましょう。
次のようなテーブル構成を想定します。
またhistoriesテーブルには以下のようなレコードが挿入されています。
| id | user_id | movie_id | created_at |
|---|---|---|---|
| 1 | 1 | 1 | 2019-01-01 00:00:00 |
| 2 | 1 | 3 | 2019-01-01 01:00:00 |
| 3 | 1 | 2 | 2019-01-01 02:00:00 |
| 4 | 1 | 2 | 2019-01-01 03:00:00 |
| 5 | 1 | 3 | 2019-01-01 04:00:00 |
| 6 | 2 | 1 | 2019-01-01 05:00:00 |
なお最終的にはActiveRecordで実現することを目指しますが、そのためにはSQLを適切に理解する必要があるので、まずはSQLから紹介していきます。
グルーピングしたうち最大値のレコードのみを取り出す
ここで少し厄介なのが、普通にgroup byとorder byを重ねるだけだと望んだとおりに降順でレコードを取得できないことです。
select * from histories
group by movie_id order by id desc;
IDが6,5,4のレコードが返ってくることが期待されますが、実際に返ってくるのは1,2,3となってしまいます。
| id | user_id | movie_id | created_at |
|---|---|---|---|
| 3 | 1 | 2 | 2019-01-01 02:00:00 |
| 2 | 1 | 3 | 2019-01-01 01:00:00 |
| 1 | 1 | 1 | 2019-01-01 00:00:00 |
そこでサブクエリとMAX関数を組み合わせて使うと、期待通りの値が取得できます。
select * from histories
where id in (select max(id) from histories group by movie_id)
order by id desc;
| id | user_id | movie_id | created_at |
|---|---|---|---|
| 6 | 2 | 1 | 2019-01-01 05:00:00 |
| 5 | 1 | 3 | 2019-01-01 04:00:00 |
| 4 | 1 | 2 | 2019-01-01 03:00:00 |
上記のクエリを叩くと確かに期待通り、IDが6,5,4の順でレコードが返ってきます。
また今回はサロゲートキーであるIDを使用しましたが、同様にcreated_atのような時刻データを活用することもできます。
select * from histories
where created_at in (select max(created_at) from histories group by movie_id)
order by created_at desc;
こちらの実行結果も先程紹介したクエリと同じものとなるので、サロゲートキーを活用しない場合でも対応可能です。
JOINしてコンテンツのレコードのみを取得する
ここまでの方法で履歴を閲覧日順に取得することができましたが、肝心のコンテンツ(movies)を降順に並べたいところです。
その場合はINNER JOIN句を使えば簡単に実現できます。
select movies.* from movies
inner join histories on histories.movie_id = movies.id
where histories.id in (select max(id) from histories group by movie_id)
order by histories.id desc;
上記のクエリの実行結果は以下のとおりです。
| id | name |
|---|---|
| 1 | 映画1 |
| 3 | 映画3 |
| 2 | 映画2 |
確かに直近閲覧された順に映画のレコードが重複なしで並んでいますね。
ActiveRecordで同様のユースケースを実現する
では本題に入り、上記のクエリを実際にActiveRecordで書いてみましょう。
ActiveRecordはお手軽なORMapperというイメージが強いですが、サブクエリやjoinを含む複雑なクエリを、Rubyの構文を活用したままほとんど実現できる表現力も備わっています。
この場合のコードは以下のようになります。
Movie.joins(:histories)
.where(histories: { id: { History.group(:movie_id).select('max(id)') } })
.order('histories.id': :desc)
このようにサブクエリも通常クエリと同様に記述することができ、結果的に発行されるSQLは手で書いたものと同じものになります。
GROUP BYを使わないクエリもある
ちなみにグルーピングしつつもGROUP BY句を使わない方法もあります。
サブクエリで定義したエイリアス付きのhistoriesで擬似的にグルーピングを行い、それより大きなIDが存在しないものをNOT EXISTS句で抽出します。
select * from movies
inner join histories as h1 on h1.movie_id = movies.id
where not exists (
select 1 from histories as h2
where h2.movie_id = h1.movie_id and h2.id > h1.id
)
order by h1.id desc;
こちらの方がやや複雑で意義に疑問を感じるかもしれませんが、先に紹介したMAX関数は単一のカラムにしか利用できませんが、こちらは複合キーにも適用できるので応用力が高い手法と言えます。
ちなみにこのクエリはWHERE句をベタ打ちしないとActiveRecordで実現することは(おそらく)できません。(※できるよというアイデアがあればコメント欄でご指摘下さい🙏)
Movie.joins(:histories)
.where('not exists (select 1 from histories as h2 where h2.movie_id = histories.movie_id and h2.id > histories.id)')
ここまで来るとSQLを全て直打ちしたほうが直感的に見えますが、このような表現方法があるのだと参考になれば幸いです。