サブクエリと闘うActiveRecord（JOIN先の最新や最大値のレコードを取得する）

# 0. 背景・こんな時に使いたいなど

ユーザー毎の最高得点や、ユーザー毎の最新の投稿などを取得・表示したいケースというのはよくあると思う。

そんな場合のSQL(ActiveRecord)は以下に記すようにある程度パターンとして決まっている気がするので、便利用に記事にしておく。

# 1. ユーザ毎の投稿の最新日時を降順で表示する

## 1-1. WHEREのサブクエリパターン

```rb
> 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で関連付けする方法](https://patorash.hatenablog.com/entry/2020/10/31/032231)
* [そこはhas_oneを使いましょうよ
](https://qiita.com/ofl/items/845b2fbda2d35fe512a2)
    * コメント上で最新のレコードが取得できていないSQLについて議論されている。

## 1-2. JOINのサブクエリパターン

```rb
> 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を取り出したい
](https://qiita.com/yuyasat/items/d7f86e1f9bdd5ed3a876#actress%E3%81%AB%E7%B4%90%E3%81%A5%E3%81%91%E3%82%89%E3%82%8C%E3%81%A6%E3%81%84%E3%82%8B%E6%98%A0%E7%94%BB%E3%81%8C%E6%9C%80%E3%82%82%E6%96%B0%E3%81%97%E3%81%84%E9%A0%86%E3%81%ABactress%E3%82%92%E5%8F%96%E3%82%8A%E5%87%BA%E3%81%97%E3%81%9F%E3%81%84)
* [【Rails x SQL】検索結果を人気順で表示する（左外部結合と副問い合わせ）
](https://qiita.com/mi-1109/items/f59f053c9c9438359879)
  * 投稿毎のいいねの多い順で表示する例。
* [Active Record テーブルの結合や結合したテーブルを利用した検索や集計やソートについて
](https://qiita.com/rhiroe/items/a1bc2714eeb55e05bcc0)
* [変更履歴を持つテーブルの設計
](https://qiita.com/ak-ymst/items/2e8e92f212c807bb09a1#comment-422f1ba13ddcd770a7ed)
    * コメントで where のサブクエリより join のサブクエリにするべきと書かれてる。「(whereのサブクエリは)相関サブクエリなので遅いです。やめたほうがいいでしょう。かわりに、導出テーブルとのjoinがおすすめです。」

## 1-3. NOT EXISTSのサブクエリパターン


```rb
> 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を使わないクエリもある](https://qiita.com/ttiger55/items/4e15ccdccadedf25c83a)
* [ActiveRecord で "最新の子" を has_one で表現する](https://qiita.com/labocho/items/9225d4e8982dd54b4853)
* [子レコードの条件で親レコードを絞り込みたいときはEXISTS句を活用しよう](https://qiita.com/jnchito/items/dac1e2fbb27ad2969376)
    * `WHERE` や `JOIN` のサブクエリよりもjnchitoさん推奨。
        * あわせて `DISTINCT` で取得するケースが非推奨として挙げられている。
* https://pganalyze.com/blog/active-record-subqueries-rails#where-not-exists

# 2. その他応用的なサブクエリの使い方

あとで読んでまとめたい。


* [Active Record で複数カラムの IN 検索を行いたい](https://ja.stackoverflow.com/questions/46805/active-record-%e3%81%a7%e8%a4%87%e6%95%b0%e3%82%ab%e3%83%a9%e3%83%a0%e3%81%ae-in-%e6%a4%9c%e7%b4%a2%e3%82%92%e8%a1%8c%e3%81%84%e3%81%9f%e3%81%84)
* [railsで複数のカラムを一つの塊とみなし、複数のキーワードでAND検索したい
](https://teratail.com/questions/149808)