0. 背景・こんな時に使いたいなど
ユーザー毎の最高得点や、ユーザー毎の最新の投稿などを取得・表示したいケースというのはよくあると思う。
そんな場合のSQL(ActiveRecord)は以下に記すようにある程度パターンとして決まっている気がするので、便利用に記事にしておく。
1. ユーザ毎の投稿の最新日時を降順で表示する
1-1. WHEREのサブクエリパターン
> User.joins(:posts).where(posts: { created_at: Post.group(:user_id).select('MAX(created_at)') }).order('posts.created_at DESC')
# => SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id"
# WHERE "posts"."created_at" IN (SELECT MAX(created_at) FROM "posts" GROUP BY "posts"."user_id")
# ORDER "posts"."created_at"
解説
-
WHEREのサブクエリ（WHERE "posts"."created_at" IN (SELECT MAX(created_at) FROM "posts" GROUP BY "posts"."user_id")）で、外部キーであるuser_idでGROUP BYを行い、その最新の投稿を取るためにMAX(created_at)をしている。
参考リンク
- has_manyの最新のデータをhas_oneで関連付けする方法
-
そこはhas_oneを使いましょうよ
- コメント上で最新のレコードが取得できていないSQLについて議論されている。
1-2. JOINのサブクエリパターン
> User.joins(%|
INNER JOIN (
SELECT
"posts"."user_id" AS user_id,
MAX(created_at) AS max_created_at
FROM
"posts"
GROUP BY
"posts"."user_id"
) AS posts_max_created_at
ON users.id = posts_max_created_at.user_id
|).order("max_created_at DESC, users.id ASC")
# => SELECT "users".* FROM "users"
# INNER JOIN (SELECT "posts"."user_id" AS user_id, MAX(created_at) max_created_at FROM "posts" GROUP BY "posts"."user_id") AS posts_max_created_at
# ON users.id = posts_max_created_at.user_id
# ORDER BY max_created_at DESC, users.id ASC
解説
-
INNER JOINのサブクエリ (SELECT "posts"."user_id" AS user_id, MAX(created_at) AS max_created_at FROM "posts" GROUP BY "posts"."user_id") を使用する方法。
参考リンク
- ActiveRecordでサブクエリ（副問い合わせ）と内部結合#actressに紐づけられている映画が最も新しい順にactressを取り出したい
-
【Rails x SQL】検索結果を人気順で表示する（左外部結合と副問い合わせ）
- 投稿毎のいいねの多い順で表示する例。
- Active Record テーブルの結合や結合したテーブルを利用した検索や集計やソートについて
-
変更履歴を持つテーブルの設計
- コメントで where のサブクエリより join のサブクエリにするべきと書かれてる。「(whereのサブクエリは)相関サブクエリなので遅いです。やめたほうがいいでしょう。かわりに、導出テーブルとのjoinがおすすめです。」
1-3. NOT EXISTSのサブクエリパターン
> User.joins(:posts).where(%|
NOT EXISTS (
SELECT
1
FROM
posts AS p2
WHERE p2.user_id = posts.user_id
AND p2.created_at > posts.created_at
)
|).order('posts.created_at DESC')
# => SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id"
# WHERE NOT EXISTS (SELECT 1 FROM "posts" AS "p2" WHERE "p2"."user_id" = "posts"."user_id" AND "p2"."created_at" > "posts"."created_at")
# ORDER BY "posts"."created_at" DESC
解説
- サブクエリで定義したエイリアス付きの
posts(p2)で擬似的にグルーピングを行い、それより大きなcreated_atが存在しないものをNOT EXISTS句で抽出する。
参考リンク
- Railsで閲覧日順など、最大値でグルーピングしたDBレコードを1クエリで取得する2つの方法#GROUP BYを使わないクエリもある
- ActiveRecord で "最新の子" を has_one で表現する
-
子レコードの条件で親レコードを絞り込みたいときはEXISTS句を活用しよう
-
WHEREやJOINのサブクエリよりもjnchitoさん推奨。- あわせて
DISTINCTで取得するケースが非推奨として挙げられている。
- あわせて
-
- https://pganalyze.com/blog/active-record-subqueries-rails#where-not-exists
2. その他応用的なサブクエリの使い方
あとで読んでまとめたい。