モチベーション
belongs_to が設定されているモデルを特定のカラムに基づいて belongs_to 単位で集約し、1レコードだけ取得したいシーンがありました。
特定のカラムに対してmaxを用いて絞り込むことはできるのですが max を利用すると [group_byに指定したカラム, maxで集約した値] の配列になってしまうため、 ActiveRecord の形式でアクセスして属性を取得することができませんでした。
なんとか ActiveRecord の形式でデータにアクセスする方法をみつけたので、自分のためにもメモを残しておこうと思い、記事を作成しました。
ER図
実行環境
下記のレポジトリにて環境を作成しています。
やりたいこと
posts を user_id でグルーピングし、 updated_at が最新のレコードのみを取得したいです！
SELECT * FROM posts
| id | description | user_id | created_at | updated_at |
|---|---|---|---|---|
| 1 | 一郎 投稿内容0 | 1 | 2024-01-08 08:54:08.477335 | 2024-01-08 09:54:08.477335 |
| 2 | 一郎 投稿内容1 | 1 | 2024-01-08 08:54:08.479087 | 2024-01-08 08:54:08.479087 |
| 3 | 二郎 投稿内容 | 2 | 2024-01-08 08:54:08.480609 | 2024-01-08 08:54:08.480609 |
↓ フィルタをかけて下記のデータだけを取得したい...！
| id | description | user_id | created_at | updated_at |
|---|---|---|---|---|
| 1 | 一郎 投稿内容0 | 1 | 2024-01-08 08:54:08.477335 | 2024-01-08 09:54:08.477335 |
| 3 | 二郎 投稿内容 | 2 | 2024-01-08 08:54:08.480609 | 2024-01-08 08:54:08.480609 |
どうやってやるか
下記の順番でやると、処理のイメージが付きやすいのでおすすめです。
- SQLで実現する方法を考える
- ActiveRecordの構文でSQLを実現する方法を考える
1. SQLで実現する方法を考える
やりたいことは下記です。
- user_idでグルーピングする
- グルーピングした中で最新の
updated_atを持つpostsのレコードを取得する
SELECT * FROM posts
INNER JOIN
(
-- user_idでグルーピングして、最新のupdated_atの値を取得する
SELECT user_id, MAX(updated_at) as max_updated_at FROM posts
GROUP BY user_id
) AS max_posts
-- 取得したuser_idとmax_updated_atを紐づけて、user_id毎に最新のupdated_atを持っているレコードのみが残るようにする
ON posts.user_id = max_posts.user_id AND posts.updated_at = max_posts.max_updated_at
2. ActiveRecordの構文でSQLを実現する方法を考える
今回のSQLはサブクエリを利用しているため、ORMでもサブクエリを利用して実装を行います。
# user_idでグルーピングしてその中でupdate_atが最新のレコードのみを取得
sub_query = Post.group(:user_id).select(:user_id, 'MAX(updated_at) as max_updated_at')
Post.joins(
<<~SQL.squish
INNER JOIN (#{sub_query.to_sql}) as max_posts
ON posts.user_id = max_posts.user_id
AND posts.updated_at = max_posts.max_updated_at
SQL
)
上記を実施した結果、下記の結果が得られます。
irb(main):002> sub_query = Post.group(:user_id).select(:user_id, 'MAX(updated_at) as max_updated_at')
Post Load (5.3ms) SELECT "posts"."user_id", MAX(updated_at) as max_updated_at FROM "posts" GROUP BY "posts"."user_id" /* loading for pp */ LIMIT ? [["LIMIT", 11]]
=> [#<Post:0x0000000110db3218 id: nil, user_id: 1>, #<Post:0x0000000110db30d8 id: nil, user_id: 2>]
irb(main):003* Post.joins(
irb(main):004" <<~SQL.squish
irb(main):005" INNER JOIN (#{sub_query.to_sql}) as max_posts
irb(main):006" ON posts.user_id = max_posts.user_id
irb(main):007" AND posts.updated_at = max_posts.max_updated_at
irb(main):008* SQL
irb(main):009> )
Post Load (0.2ms) SELECT "posts".* FROM "posts" INNER JOIN (SELECT "posts"."user_id", MAX(updated_at) as max_updated_at FROM "posts" GROUP BY "posts"."user_id") as max_posts ON posts.user_id = max_posts.user_id AND posts.updated_at = max_posts.max_updated_at /* loading for pp */ LIMIT ? [["LIMIT", 11]]
=>
[#<Post:0x0000000111271548
id: 1,
description: "一郎 投稿内容0",
user_id: 1,
created_at: Mon, 08 Jan 2024 09:50:10.191154000 UTC +00:00,
updated_at: Mon, 08 Jan 2024 10:50:10.187172000 UTC +00:00>,
#<Post:0x0000000111271408
id: 3,
description: "二郎 投稿内容",
user_id: 2,
created_at: Mon, 08 Jan 2024 09:50:10.194208000 UTC +00:00,
updated_at: Mon, 08 Jan 2024 09:50:10.194208000 UTC +00:00>]
クエリは下記になっていて、ActiveRecordで意図したコードが実装できています 😄
SELECT
"posts".*
FROM
"posts"
INNER JOIN
(
SELECT
"posts"."user_id",
MAX(updated_at) as max_updated_at
FROM
"posts"
GROUP BY
"posts"."user_id"
) as max_posts
ON posts.user_id = max_posts.user_id
AND posts.updated_at = max_posts.max_updated_at
所感
ActiveRecordでサブクエリを利用する方法を理解することができました。
クエリを組み立てる時点でも結構困惑しましたが、なんとか実装できてよかったです 😎
他のカラムで絞り込みたい場合にも、同様の手法で絞り込みすることができそうなので、良い学びになりました！