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

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

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

結論

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

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

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

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

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

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

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 を呼ぶことでカテゴリ別の予約件数を取得できました。

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) を使った方法をコメントくださいました。
確かに、こちらの方がスッキリ書けます。ありがとうございます。

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

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

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

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した時に各レストランのコースの数だけレコードが重複してしまうことが原因です。

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