10
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated at

Organization

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句のサブクエリとして処理できます。

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

Untitled_subquery-erd_-_Cacoo.png

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

  • 和食とイタリアンを扱うレストラン(大衆系レストラン)とイタリアンのみ扱うレストラン(高級レストラン)がある
  • それぞれ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.namereservations.idSELECT DISTINCT するサブクエリを定義します。(subquery変数に代入)

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

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 することで、重複を排除した件数を取得できます。

参考

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Sign upLogin
10
Help us understand the problem. What are the problem?