QA@IT
«質問へ戻る

表現を若干修正したり、メソッド名やテーブル名をバッククオートで囲ったりしました。

262
本文
-[『SQL アンチパターン』](http://www.oreilly.co.jp/books/9784873115894/) 7 章に以下のような SQL が出てきます。バグに複数のタグを付けられるような設計において、 'printing' タグが付き、かつ 'performance' タグが付いているバグの検索を行う SQL です。
+[『SQL アンチパターン』](http://www.oreilly.co.jp/books/9784873115894/)の第 7 章「マルチカラムアトリビュート(複数列属性)」に、以下のような SQL が出てきます。バグに複数のタグを付けられるような設計において、 'printing' タグが付き、かつ 'performance' タグが付いているバグの検索を行う SQL です。
 
 ```sql
 SELECT * FROM Bugs
 
 少し例を変えて、映画に複数のカテゴリが付けられるような設計を考えます。
 
-例えば movies テーブルと categories テーブルを交差テーブル categories_movies (少し違和感がある名前ですが、 Rails デフォルトの規約では辞書順で交差テーブル名を付けます) で関連付け、 has_and_belongs_to_many で多対多の構造を作ります。
+例えば `movies` テーブルと `categories` テーブルを交差テーブル `categories_movies` で関連付け(少し違和感がある名前ですが、 Rails デフォルトの規約では辞書順で交差テーブル名を付けるとなっています) 、 `has_and_belongs_to_many` で多対多の構造を作っているとします。
 
 ```ruby
 # == Schema Information
 end
 ```
 
-Movie モデルの categorized_as スコープは、 categories と join して Category.name_eq スコープを merge して持ってくれば良さそうに見えます。
+`Movie` モデルの `categorized_as` スコープは、 `Category` モデルから `Category.name_eq` スコープを `merge` して持ってくれば良さそうに見えます。
 
 ```ruby
 # == Schema Information
   (imdb_url IS NOT NULL)
 ```
 
-めでたしめでたし、でしょうか?
+これにて、めでたしめでたし……でしょうか?
 
-……あまりにも力業が過ぎるように思えます。Rails のことですから、もっと洗練された方法はありませんでしょうか?
+……あまりにも力業が過ぎるように思えます。もう少し良い方法がありませんでしょうか? Rails のことですから、もっと洗練された方法がありそうに思えるのです。
 
 ----
 
 (追記) 書き漏らしました。検証環境は ruby 1.9.3-p392 & rails 3.2.13 です。
-また、 has_and_belongs_to_many ではなく has_many :through のときはこうする/こうできる、というようなものがありましたら、こちらもぜひ教えてください。
+
+また、 `has_and_belongs_to_many` ではなく `has_many :through` のときはこうする/こうできる、というようなものがありましたら、こちらも教えて頂けましたら幸いです。

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

『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 のときはこうする/こうできる、というようなものがありましたら、こちらも教えて頂けましたら幸いです。

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

```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 アンチパターン』書評エントリ](http://iakio.hatenablog.com/entry/2013/03/31/205348)に

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

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

----

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

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

```ruby
# == 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` して持ってくれば良さそうに見えます。

```ruby
# == 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 部分)
```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 部分)
```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 毎にテーブルに別名を付ければ良いと考えるなら、愚直に実装すると以下のようになってしまいそうです。

```ruby
# == 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 部分)
```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 部分)
```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` のときはこうする/こうできる、というようなものがありましたら、こちらも教えて頂けましたら幸いです。

SQL 部分を整形しました

262
本文
 
 (上記 SQL 部分)
 ```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'
+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' カテゴリのみの検索になっているように見えます。
 
 (上記 SQL 部分)
 ```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'
+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'
 ```
 
 
 
 (上記 SQL 部分)
 ```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')
+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')
 ```
 
 長いスコープチェーンも期待通りに動きます。
 
 (上記 SQL 部分)
 ```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)
+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 で同じテーブルを何度も JOIN する上手い方法はありますか?

『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 スコープは、 categories と join して 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 のときはこうする/こうできる、というようなものがありましたら、こちらもぜひ教えてください。

[『SQL アンチパターン』](http://www.oreilly.co.jp/books/9784873115894/) 7 章に以下のような SQL が出てきます。バグに複数のタグを付けられるような設計において、 'printing' タグが付き、かつ 'performance' タグが付いているバグの検索を行う SQL です。

```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 アンチパターン』書評エントリ](http://iakio.hatenablog.com/entry/2013/03/31/205348)に

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

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

----

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

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

```ruby
# == 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 スコープは、 categories と join して Category.name_eq スコープを merge して持ってくれば良さそうに見えます。

```ruby
# == 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 部分)
```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 部分)
```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 毎にテーブルに別名を付ければ良いと考えるなら、愚直に実装すると以下のようになってしまいそうです。

```ruby
# == 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 部分)
```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 部分)
```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 のときはこうする/こうできる、というようなものがありましたら、こちらもぜひ教えてください。

検証環境や SQL 出力について追記しました

262
本文
  => 251
 ```
 
+(上記 SQL 部分)
+```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 :003 >
 ```
 
+(上記 SQL 部分)
+```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 毎にテーブルに別名を付ければ良いと考えるなら、愚直に実装すると以下のようになってしまいそうです。
 
 ```ruby
 1.9.3-p392 :002 >
 ```
 
+(上記 SQL 部分)
+```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 :003 >
 ```
 
+(上記 SQL 部分)
+```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 のときはこうする/こうできる、というようなものがありましたら、こちらもぜひ教えてください。

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

『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 スコープは、 categories と join して 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 のときはこうする/こうできる、というようなものがありましたら、こちらもぜひ教えてください。

[『SQL アンチパターン』](http://www.oreilly.co.jp/books/9784873115894/) 7 章に以下のような SQL が出てきます。バグに複数のタグを付けられるような設計において、 'printing' タグが付き、かつ 'performance' タグが付いているバグの検索を行う SQL です。

```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 アンチパターン』書評エントリ](http://iakio.hatenablog.com/entry/2013/03/31/205348)に

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

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

----

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

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

```ruby
# == 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 スコープは、 categories と join して Category.name_eq スコープを merge して持ってくれば良さそうに見えます。

```ruby
# == 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 部分)
```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 部分)
```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 毎にテーブルに別名を付ければ良いと考えるなら、愚直に実装すると以下のようになってしまいそうです。

```ruby
# == 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 部分)
```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 部分)
```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 のときはこうする/こうできる、というようなものがありましたら、こちらもぜひ教えてください。

質問を投稿

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

『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 スコープは、 categories と join して 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

カテゴリ '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 >

重複しないように 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 >

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

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 >

めでたしめでたし、でしょうか?

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

[『SQL アンチパターン』](http://www.oreilly.co.jp/books/9784873115894/) 7 章に以下のような SQL が出てきます。バグに複数のタグを付けられるような設計において、 'printing' タグが付き、かつ 'performance' タグが付いているバグの検索を行う SQL です。

```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 アンチパターン』書評エントリ](http://iakio.hatenablog.com/entry/2013/03/31/205348)に

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

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

----

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

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

```ruby
# == 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 スコープは、 categories と join して Category.name_eq スコープを merge して持ってくれば良さそうに見えます。

```ruby
# == 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
```

カテゴリ '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 >
```

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

```ruby
# == 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 >
```

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

```
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 >
```

めでたしめでたし、でしょうか?

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