QA@IT

Rails で同じテーブルを何度も JOIN する上手い方法はありますか?

14187 PV

『SQL アンチパターン』の第 7 章「マルチカラムアトリビュート(複数列属性)」に、以下のような SQL が出てきます。バグに複数のタグを付けられるような設計において、 'printing' タグが付き、かつ 'performance' タグが付いているバグの検索を行う SQL です。

SELECT * FROM Bugs
  INNER JOIN Tags AS t1 USING (bug_id)
  INNER JOIN Tags AS t2 USING (bug_id)
WHERE t1.tag = 'printing' AND t2.tag = 'performance';

id:iakio さんの『SQL アンチパターン』書評エントリ

ただ、Tagsテーブルにt1、t2と2種類のaliasをつけてJOINしているあたりがいかにもO/Rマッパーと相性が悪そうだ。
Railsなどではどのように解決するのが普通なのだろう。

とあります。確かに、少し相性が悪そうに見えます。


少し例を変えて、映画に複数のカテゴリが付けられるような設計を考えます。

例えば movies テーブルと categories テーブルを交差テーブル categories_movies で関連付け(少し違和感がある名前ですが、 Rails デフォルトの規約では辞書順で交差テーブル名を付けるとなっています) 、 has_and_belongs_to_many で多対多の構造を作っているとします。

# == Schema Information
#
# Table name: categories
#
#  id         :integer          not null, primary key
#  name       :string(20)       not null
#  created_at :datetime         not null
#  updated_at :datetime         not null
#

class Category < ActiveRecord::Base
  attr_accessible :name

  has_and_belongs_to_many :movies

  scope :name_eq, -> name { where(name: name) }
end

Movie モデルの categorized_as スコープは、 Category モデルから Category.name_eq スコープを merge して持ってくれば良さそうに見えます。

# == Schema Information
#
# Table name: movies
#
#  id          :integer          not null, primary key
#  title       :string(255)      not null
#  released_on :date
#  imdb_url    :string(255)
#  created_at  :datetime         not null
#  updated_at  :datetime         not null
#

class Movie < ActiveRecord::Base
  attr_accessible :title, :released_on, :imdb_url

  has_and_belongs_to_many :categories

  scope :with_imdb,    -> { where("imdb_url IS NOT NULL") }
  scope :without_imdb, -> { where("imdb_url IS NULL") }

  scope :eighties, -> { where("released_on BETWEEN ? AND ?", '1980-01-01', '1989-12-31') }
  scope :nineties, -> { where("released_on BETWEEN ? AND ?", '1990-01-01', '1999-12-31') }

  scope :categorized_as, -> name { joins(:categories).merge(Category.name_eq(name)) }
end

実際に一つのカテゴリに関するスコープは上手く動作しているように見えますが、

1.9.3-p392 :001 > Movie.categorized_as('Action').count
   (1.6ms)  SELECT COUNT(*) FROM "movies" INNER JOIN "categories_movies" ON "categories_movies"."movie_id" = "movies"."id" INNER JOIN "categories" ON "categories"."id" = "categories_movies"."category_id" WHERE "categories"."name" = 'Action'
 => 251

(上記 SQL 部分)

SELECT
  COUNT(*)
FROM
  "movies"
  INNER JOIN "categories_movies"
    ON "categories_movies"."movie_id" = "movies"."id"
  INNER JOIN "categories"
    ON "categories"."id" = "categories_movies"."category_id"
WHERE
  "categories"."name" = 'Action'

カテゴリ 'Action' かつカテゴリ 'Adventure' のスコープチェーンは上手く動きませんでした。後勝ち、つまり 'Adventure' カテゴリのみの検索になっているように見えます。

1.9.3-p392 :002 > Movie.categorized_as('Action').categorized_as('Adventure').count
   (2.2ms)  SELECT COUNT(*) FROM "movies" INNER JOIN "categories_movies" ON "categories_movies"."movie_id" = "movies"."id" INNER JOIN "categories" ON "categories"."id" = "categories_movies"."category_id" WHERE "categories"."name" = 'Adventure'
 => 135
1.9.3-p392 :003 >

(上記 SQL 部分)

SELECT
  COUNT(*)
FROM
  "movies"
  INNER JOIN "categories_movies"
    ON "categories_movies"."movie_id" = "movies"."id"
  INNER JOIN "categories"
    ON "categories"."id" = "categories_movies"."category_id"
WHERE
  "categories"."name" = 'Adventure'

重複しないように JOIN 毎にテーブルに別名を付ければ良いと考えるなら、愚直に実装すると以下のようになってしまいそうです。

# == Schema Information
#
# Table name: movies
#
#  id          :integer          not null, primary key
#  title       :string(255)      not null
#  released_on :date
#  imdb_url    :string(255)
#  created_at  :datetime         not null
#  updated_at  :datetime         not null
#

class Movie < ActiveRecord::Base
  attr_accessible :title, :released_on, :imdb_url

  has_and_belongs_to_many :categories

  scope :with_imdb,    -> { where("imdb_url IS NOT NULL") }
  scope :without_imdb, -> { where("imdb_url IS NULL") }

  scope :eighties, -> { where("released_on BETWEEN ? AND ?", '1980-01-01', '1989-12-31') }
  scope :nineties, -> { where("released_on BETWEEN ? AND ?", '1990-01-01', '1999-12-31') }

  def self.categorized_as(category_name)
    categories_movies_alias = random_alias_for(:categories_movies)
    categories_alias = random_alias_for(:categories)
    joins("INNER JOIN categories_movies AS #{categories_movies_alias} ON #{categories_movies_alias}.movie_id = movies.id INNER JOIN categories AS #{categories_alias} ON #{categories_alias}.id = #{categories_movies_alias}.category_id").where("#{categories_alias}.name = ?", category_name)
  end

  def self.random_alias_for(table_name)
    "#{table_name}_#{('a'..'z').to_a.shuffle[0..2].join}"
  end
end

これで、望み通りカテゴリ 'Action' かつカテゴリ 'Adventure' のスコープチェーンは動作します。

1.9.3-p392 :001 > Movie.categorized_as('Action').categorized_as('Adventure').count
   (3.4ms)  SELECT COUNT(*) FROM "movies" INNER JOIN categories_movies AS categories_movies_gmh ON categories_movies_gmh.movie_id = movies.id INNER JOIN categories AS categories_fvj ON categories_fvj.id = categories_movies_gmh.category_id INNER JOIN categories_movies AS categories_movies_ygf ON categories_movies_ygf.movie_id = movies.id INNER JOIN categories AS categories_kcg ON categories_kcg.id = categories_movies_ygf.category_id WHERE (categories_fvj.name = 'Action') AND (categories_kcg.name = 'Adventure')
 => 75
1.9.3-p392 :002 >

(上記 SQL 部分)

SELECT
  COUNT(*)
FROM
  "movies"
  INNER JOIN categories_movies AS categories_movies_gmh
    ON categories_movies_gmh.movie_id = movies.id
  INNER JOIN categories AS categories_fvj
    ON categories_fvj.id = categories_movies_gmh.category_id
  INNER JOIN categories_movies AS categories_movies_ygf
    ON categories_movies_ygf.movie_id = movies.id
  INNER JOIN categories AS categories_kcg
    ON categories_kcg.id = categories_movies_ygf.category_id
WHERE
  (categories_fvj.name = 'Action')
AND
  (categories_kcg.name = 'Adventure')

長いスコープチェーンも期待通りに動きます。

1.9.3-p392 :002 > Movie.eighties.categorized_as('Action').categorized_as('Adventure').categorized_as('Comedy').with_imdb.count
   (4.5ms)  SELECT COUNT(*) FROM "movies" INNER JOIN categories_movies AS categories_movies_qgs ON categories_movies_qgs.movie_id = movies.id INNER JOIN categories AS categories_wyx ON categories_wyx.id = categories_movies_qgs.category_id INNER JOIN categories_movies AS categories_movies_cnv ON categories_movies_cnv.movie_id = movies.id INNER JOIN categories AS categories_iey ON categories_iey.id = categories_movies_cnv.category_id INNER JOIN categories_movies AS categories_movies_smw ON categories_movies_smw.movie_id = movies.id INNER JOIN categories AS categories_cwx ON categories_cwx.id = categories_movies_smw.category_id WHERE (released_on BETWEEN '1980-01-01' AND '1989-12-31') AND (categories_wyx.name = 'Action') AND (categories_iey.name = 'Adventure') AND (categories_cwx.name = 'Comedy') AND (imdb_url IS NOT NULL)
 => 2
1.9.3-p392 :003 >

(上記 SQL 部分)

SELECT
  COUNT(*)
FROM
  "movies"
  INNER JOIN categories_movies AS categories_movies_qgs
    ON categories_movies_qgs.movie_id = movies.id
  INNER JOIN categories AS categories_wyx
    ON categories_wyx.id = categories_movies_qgs.category_id
  INNER JOIN categories_movies AS categories_movies_cnv
    ON categories_movies_cnv.movie_id = movies.id
  INNER JOIN categories AS categories_iey
    ON categories_iey.id = categories_movies_cnv.category_id
  INNER JOIN categories_movies AS categories_movies_smw
    ON categories_movies_smw.movie_id = movies.id
  INNER JOIN categories AS categories_cwx
    ON categories_cwx.id = categories_movies_smw.category_id
WHERE
  (released_on BETWEEN '1980-01-01' AND '1989-12-31')
AND
  (categories_wyx.name = 'Action')
AND
  (categories_iey.name = 'Adventure')
AND
  (categories_cwx.name = 'Comedy')
AND
  (imdb_url IS NOT NULL)

これにて、めでたしめでたし……でしょうか?

……あまりにも力業が過ぎるように思えます。もう少し良い方法がありませんでしょうか? Rails のことですから、もっと洗練された方法がありそうに思えるのです。


(追記) 書き漏らしました。検証環境は ruby 1.9.3-p392 & rails 3.2.13 です。

また、 has_and_belongs_to_many ではなく has_many :through のときはこうする/こうできる、というようなものがありましたら、こちらも教えて頂けましたら幸いです。

回答

タイトルにあるJOINではありませんが、サブクエリを使って同様のことが出来そうです。サブクエリですと、別名を付けなくてすみますから、多少はシンプルになりそうです。

where(id: ...)のキー部分にActiveRecord::Relationを渡すと、IN ()を使ったサブクエリに展開されます。これで外部への影響がないWHERE句が作れますので、あとはそれをたたみ込めばよいのではないでしょうか。

%w[Fantasy Middle-earth].inject(Movie.scoped) {|rel, category|
  rel.where(id: Category.where(name: category).joins('INNER JOIN categories_movies cm ON cm.category_id = categories.id').select('cm.movie_id'))
}
=> [#<Movie id: 1, title: "Hobbit", released_on: nil, imdb_url: nil, created_at: "2013-04-02 14:51:10", updated_at: "2013-04-02 14:51:10">]

発行されるSQLは以下のようになります。冒頭に書いたようにJOINは使っていないものの、これはこれで理屈としてはおかしくないSQLのように見えます。いかがでしょうか。

SELECT "movies".* 
  FROM "movies"
 WHERE "movies"."id" IN (
     SELECT cm.movie_id FROM "categories"
     INNER JOIN categories_movies cm ON cm.category_id = categories.id
     WHERE "categories"."name" = 'Fantasy'
  ) AND "movies"."id" IN (
     SELECT cm.movie_id FROM "categories"
     INNER JOIN categories_movies cm ON cm.category_id = categories.id
     WHERE "categories"."name" = 'Middle-earth'
  )

ただ、hbtmだと交差テーブルをアプリケーションコードから扱いづらいため、けっこうモリっとSQL"文字列"を書いてしまう感じですね。

コレがmovie_categorizationsを交差テーブルにしたhas_many :throughですと、交差テーブルもモデルとして遇せるために、もうちょっとキレイになります。

%w[Fantasy Middle-earth].inject(Movie.scoped) {|rel, category|
  rel.where(id: MovieCategorization.select(:movie_id).joins(:category).where(categories: {name: category}))
}

SQLはこんなかんじです。

SELECT "movies".*
  FROM "movies"
 WHERE "movies"."id" IN (
     SELECT movie_id
       FROM "movie_categorizations"
      INNER JOIN "categories" ON "categories"."id" = "movie_categorizations"."category_id"
      WHERE "categories"."name" = 'Fantasy'
) AND "movies"."id" IN (
     SELECT movie_id
       FROM "movie_categorizations"
      INNER JOIN "categories" ON "categories"."id" = "movie_categorizations"."category_id"
      WHERE "categories"."name" = 'Middle-earth'
)
編集 履歴 (1)
  • ありがとうございます! なるほど `Movie.scoped` を起点に `inject` していくわけですね。 また、合わせて `has_many :through` の場合も説明くださり、非常に参考になりました。やはり `has_many :through` の方がレールに乗っているのですね。 -

SQL 的なアプローチを Rails になじませる実装として、私なら下の様に書きます。

class Movie < ActiveRecord::Base
  has_and_belongs_to_many :categories
  scope :categorized_as, ->(*names) do
    target_ids = self.
      select("movies.id").
      joins(:categories).
      merge(Category.where(name: names)).
      group("movies.id").
      having("count(distinct categories.name) = ?", names.size)

    where(id: target_ids)
  end
end
irb(main):018:0> Movie.categorized_as('Action', 'Adventure').count

SQL は下のようになります。

SELECT COUNT(*) 
  FROM "movies"
 WHERE "movies"."id" IN (
       SELECT movies.id
         FROM "movies"
   INNER JOIN "categories_movies"
           ON "categories_movies"."movie_id" = "movies"."id"
   INNER JOIN "categories"
           ON "categories"."id" = "categories_movies"."category_id"
        WHERE "categories"."name" IN ('Action', 'Adventure')
     GROUP BY movies.id
       HAVING count(distinct categories.name) = 2
 )

これなら、メソッドチェインに組み込めますし、Movie モデルを使う人があまり悩まずに使えるようになると思います。

編集 履歴 (5)
  • これはとてもエレガントな回答ですね!! すばらしいです。 -

別解を思いつきました。
PostgreSQL 8.4以上限定ですが、配列関数を駆使するとなかなか感じよくかけます。

subquery = Category.
  select('ARRAY_AGG(name::text)').
  joins(:movies).
  where('movie_categorizations.movie_id = movies.id')

Movie.where("(#{subquery.to_sql}) @> ARRAY[?]", ['Fantasy', 'Middle-earth'])

上記の回答に比べるとアレーっていうほど簡単ですね。もしPostgreSQLを使ってるようであればお試しあれ〜!!
(関数インデックスつけないと、あまりの遅さにあれー?ってなりそうですけどね...)

編集 履歴 (2)
  • おおおこれはシンプル!! ありがとうございます! -

SQLアンチパターンの例を用いて回答してみます。
moroさんの回答とそんなに変わらないですが。

habtmではなく、has_many :through関連を前提として話を進めます。
to_sqlを使って、joinsの中でSELECT文を生成するという方法で記述した事があります。
JOINしてから絞りこむのではなく、関連テーブルを先にSELECTして導出したテーブルに番号を振って、JOINしてます。

tag1 = Tag.where(tag: "printing")
tag2 = Tag.where(tag: "performance")
[tag1, tag2].each_with_index.inject(Bug.scoped) {|bug_scope, (tag_scope, i)|
  bug_scope.joins("INNER JOIN (#{tag_scope.to_sql}) AS tags#{i} ON tags#{i}.bug_id = bugs.id")
}
SELECT `bugs`.* FROM `bugs` INNER JOIN (SELECT `tags`.* FROM `tags` WHERE `tags`.`tag` = "printing") AS tags0 ON tags0.bug_id = bugs.id INNER JOIN (SELECT `tags`.* FROM `tags` WHERE `tags`.`tag` = "performance") AS tags1 ON tags1.bug_id = bugs.id

injectはmoroさんの回答を見てカッコ良かったので、パクりましたw
条件が動的に増減しないなら、joinsに直接書いちゃえば、もうちょっと見通し良くなると思います。

これで、同様の結果が返るはずです。
個人的にはWHERE INの中でサブクエリを使うより、こちらの方がクエリのイメージが分かりやすいのですが、どちらの方が効率良いのかなーと思ってます。

編集 履歴 (2)
  • ご回答ありがとうございます! なるほど scoped 起点の inject の中で各条件を示した ActiveRecord::Relation を to_sql してつなげていくわけですね。こちらも勉強になります。 -
ウォッチ

この質問への回答やコメントをメールでお知らせします。