ActiveRecordを使ってFROM句のサブクエリを書く方法

ActiveRecordで複数テーブルを `JOIN` して `GROUP BY` する処理を書く時、 `GROUP BY` する前に重複排除を目的とした `DISTINCT` をする必要があり**「FROM句のサブクエリを書く」**というシチュエーションに遭遇しました。

SQLだと簡単に書けるのですがActiveRecordだとどう書くべきかわからなかったので、まとめます。

## 結論
先にサブクエリに相当する処理をActiveRecordで記述し変数に代入しておき、

```ruby
ModelClass.from("(#{subquery.to_sql}) AS sub").select('sub.columnA, sub.columnB')
```

のように `from` メソッドを呼び出すことでFROM句のサブクエリとして処理できます。

## 遭遇したシチュエーション

<img width="1020" alt="Untitled_subquery-erd_-_Cacoo.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/60996/bce1d215-e77d-1990-587a-e304759467be.png">

各テーブルは以下のようになっています。
要約するとこういった状況です。

> - 和食とイタリアンを扱うレストラン(大衆系レストラン)とイタリアンのみ扱うレストラン(高級レストラン)がある
> - それぞれ3件ずつの予約が入っている
> - イタリアンを扱うレストランの予約件数(3+3=6)と和食を扱うレストランの予約件数(3)を取得したい

```sql
SELECT id, restaurant_id, guest_name, reserved_at FROM reservations;
1|1|岩瀬 好近|2019-08-01 13:00:00
2|1|戸田 良市|2019-08-01 14:00:00
3|2|森 信好|2019-08-01 15:00:00
4|2|平山 利次|2019-08-01 11:00:00
5|1|金城 義勝|2019-08-01 18:00:00
6|2|関 知実|2019-08-01 19:00:00

SELECT id, name FROM restaurants;
1|大衆系レストラン
2|高級レストラン

SELECT id, restaurant_id, name FROM courses;
1|1|焼き魚コース
2|1|串カツ三昧
3|1|寿司セット
4|1|コラボピザ
5|2|高級ピッツア
6|2|色とりどりのパスタ
7|2|イタリアンフルコース

SELECT id, restaurant_id, name, category_id FROM courses;
1|1|焼き魚コース|1
2|1|串カツ三昧|1
3|1|寿司セット|1
4|1|コラボピザ|2
5|2|高級ピッツア|2
6|2|色とりどりのパスタ|2
7|2|イタリアンフルコース|2

SELECT id, name  FROM categories;
1|和食
2|イタリアン
```

## 解決方法

各テーブルをJOINして、 `categories.name` と `reservations.id` を `SELECT DISTINCT` するサブクエリを定義します。(subquery変数に代入)

`from` メソッドにsubqueryをサブクエリに展開して渡し、 `group` → `select` を呼ぶことでカテゴリ別の予約件数を取得できました。

```ruby
subquery = Reservation.all
                      .joins(restaurant: { courses: :category })
                      .select(%(
                        distinct categories.name AS category_name,
                                 reservations.id AS reservation_id
                      ))

reservations = Reservation.from("(#{subquery.to_sql}) AS reservations")
                          .group('reservations.category_name')
                          .select(%(
                            reservations.category_name AS category_name,
                            COUNT(reservations.reservation_id) AS reservation_count
                          ))

reservations.map do|reservation|
  [reservation.category_name, reservation.reservation_count]
end
=> [["イタリアン", 6], ["和食", 3]]
```

(2019/8/21 13:38追記)
@jnchito さんが `COUNT(DISTINCT some_column)` を使った方法をコメントくださいました。
確かに、こちらの方がスッキリ書けます。ありがとうございます。

```ruby
Reservation
  .joins(restaurant: { courses: :category })
  .group('categories.name')
  .distinct
  .count(:id)
```

## (参考)サブクエリを使わず誤った値を取得してしまった事例

参考までに最初に書いたコードを晒します。 **このコードは正しい予約件数を取得できません。**

```ruby
reservations = Reservation.all
                          .joins(restaurant: { courses: :category })
                          .group('categories.name')
                          .select(%(
                            categories.name AS category_name, 
                            COUNT(reservations.id) AS reservation_count
                          ))

reservations.map do|reservation|
  [reservation.category_name, reservation.reservation_count]
end
=> [["イタリアン", 12], ["和食", 9]]
```

原因はGROUP BYする直前のテーブルを参照すると一目瞭然です。
1つのレストランに複数のコースが存在することで、JOINした時に各レストランのコースの数だけレコードが重複してしまうことが原因です。

```SQL
SELECT reservations.id,
       restaurants.NAME,
       courses.NAME,
       categories.NAME
FROM   "reservations"
       INNER JOIN "restaurants"
               ON "restaurants"."id" = "reservations"."restaurant_id"
       INNER JOIN "courses"
               ON "courses"."restaurant_id" = "restaurants"."id"
       INNER JOIN "categories"
               ON "categories"."id" = "courses"."category_id";
1|大衆系レストラン|焼き魚コース|和食
2|大衆系レストラン|焼き魚コース|和食
5|大衆系レストラン|焼き魚コース|和食
1|大衆系レストラン|串カツ三昧|和食
2|大衆系レストラン|串カツ三昧|和食
5|大衆系レストラン|串カツ三昧|和食
1|大衆系レストラン|寿司セット|和食
2|大衆系レストラン|寿司セット|和食
5|大衆系レストラン|寿司セット|和食
1|大衆系レストラン|コラボピザ|イタリアン
2|大衆系レストラン|コラボピザ|イタリアン
5|大衆系レストラン|コラボピザ|イタリアン
3|高級レストラン|高級ピッツア|イタリアン
4|高級レストラン|高級ピッツア|イタリアン
6|高級レストラン|高級ピッツア|イタリアン
3|高級レストラン|色とりどりのパスタ|イタリアン
4|高級レストラン|色とりどりのパスタ|イタリアン
6|高級レストラン|色とりどりのパスタ|イタリアン
3|高級レストラン|イタリアンフルコース|イタリアン
4|高級レストラン|イタリアンフルコース|イタリアン
6|高級レストラン|イタリアンフルコース|イタリアン
```

前項のようにサブクエリで `DISTINCT` した上で `GROUP BY` することで、重複を排除した件数を取得できます。

## 参考

- [Constructing a select * from subquery using ActiveRecord](http://joshfrankel.me/blog/constructing-a-sql-select-from-subquery-in-activerecord/)