Arel でサブクエリ


Rails Advent Calendar 8日目です。

Arel でサブクエリを組んでみようと思って、どうやるのかググるとこのページがヒット。いろんな例があってわかりやすいです。

[Arelで色んなSQLを組み立ててみる - ryopekoの日記](http://d.hatena.ne.jp/ryopeko/20101215/1292373612)

> サブクエリや型変換を伴うようなSQLの組み上げについては調査中です。

えー。
ということで調べたメモ。


## 基本
Arel::Table というクラスがあって、そこからメソッドをチェーンしていきます。
Arel::Table のオブジェクトは普通にnewして作ることができます。

```ruby
books = Arel::Table.new(:books)
```

または、ActiveRecordのモデルからは arel_table というメソッドで取れます。

```ruby
class Book < ActiveRecord::Base
  ...
end

books = Book.arel_table
```

`books[:id]`のように`[]`を使うと、 Arel::Attributes::Attribute というカラムを表すクラスのオブジェクトが取れます。これで条件を組み立てていきます。

## 例
ryopekoさんの記事からほぼそのまま引用させていただきます。

```ruby
books
  .project(Arel.star)
  .where(books[:id].gt(5))
  .where(books[:id].lt(10))
  .to_sql
# => SELECT * FROM "books" WHERE "books"."id" > 5 AND "books"."id" < 10

books
  .project(books[:id].count.as('id_count'), books[:category_id])
  .group(books[:category_id])
  .to_sql
# => SELECT COUNT("books"."id") AS id_count, "books"."category_id" FROM "books" GROUP BY "books"."category_id"
```

to_sql メソッドでSQL文字列が得られます。
説明するまでもないぐらい直感的ですね。

## サブクエリ

### JOIN, FROMなどでテーブルとして使うサブクエリ
books から project などを呼ぶと Arel::SelectManager のオブジェクトになり、そのままずっとメソッドチェーンしていけるのですが、 SelectManager のままでは join などに渡すことができません（エラーになる）し、`[]`で Attribute を指定することもできません。
最後に as メソッドを使ってテーブル名をつけると、 Arel::TableAlias のオブジェクトになって Arel::Table と同様に扱えます。
<small>これがどこにも書いてなかった…。</small>

```ruby
counts =
books
  .project(books[:id].count.as('id_count'), books[:category_id])
  .group(books[:category_id])
  .as('counts')

categories = Arel::Table.new(:categories)

categories
  .project(Arel.star)
  .join(counts, Arel::Nodes::OuterJoin)
  .on(categories[:id].eq(counts[:category_id]))
  .to_sql
# => SELECT * FROM "categories" LEFT OUTER JOIN (SELECT COUNT("books"."id") AS id_count, "books"."category_id" FROM "books" GROUP BY "books"."category_id") counts ON "categories"."id" = counts."category_id"
```

FROMに置きたい場合、 Arel::TableAlias からは project などを直接呼べないのですが、適当な Arel::Table から from を使えば呼ぶことができます。このとき Table は何の意味があるのか謎。


ActiveRecordでは、whereなどでチェーンしていくと ActiveRecord::Relation になりますが、そこから arel というメソッドで Arel::SelectManager を得ることができます。
なので、こういうミックスも可能。
<small>内部でArel使ってるからできて当たり前ではありますが…</small>

```ruby
counts_ar =
Book
  .select("COUNT(books.id) AS id_count, books.category_id")
  .group(:category_id)
  .arel
  .as('counts')

categories
  .project(Arel.star)
  .join(counts_ar, Arel::Nodes::OuterJoin)
  .on(categories[:id].eq(counts_ar[:category_id]))
  .to_sql
# => SELECT * FROM "categories" LEFT OUTER JOIN (SELECT COUNT(books.id) AS id_count, books.category_id FROM "books" GROUP BY category_id) counts ON "categories"."id" = counts."category_id"
```

### WHEREで使うサブクエリ
WHERE条件の中でもサブクエリが使えますが、その場合は

```ruby
ids_10 = books
  .project(books[:category_id])
  .group(books[:category_id])
  .having(books[:id].count.gteq(10))

categories
  .project(Arel.star)
  .where(categories[:id].in(ids_10))
  .to_sql
# => SELECT * FROM "categories"  WHERE "categories"."id" IN (SELECT "books"."category_id" FROM "books"  GROUP BY "books"."category_id" HAVING COUNT("books"."id") >= 10)
```
のように in には SelectManager がそのまま渡せます。
INでのサブクエリはたしかActiveRecordでもできましたね。

INを使わずに、`WHERE id = (…)`の形も書けるはず、と思ったのですが、 eq は SelectManager を受け取ってくれません。
 ast メソッドを使って SelectStatement に変換すると通るのですが、生成されるSQLに`( )`が入ってない！

```ruby
id_10 =
books
  .project(books[:category_id])
  .group(books[:category_id])
  .having(books[:id].count.eq(10))
  .take(1)

categories
  .project(Arel.star)
  .where(categories[:id].eq(id_10))
# => TypeError: Cannot visit Arel::SelectManager

categories
  .project(Arel.star)
  .where(categories[:id].eq(id_10.ast))
  .to_sql
# => SELECT * FROM "categories" WHERE "categories"."id" = SELECT "books"."category_id" FROM "books" GROUP BY "books"."category_id" HAVING COUNT("books"."id") = 10 LIMIT 1
```

<del>どうやればいいかよくわかりませんでした。
まあ、これはIN使ってもJOIN使っても書けるからいらないですね（笑）</del>
<ins>同じく as を使って Arel::TableAlias にすればOKでした。なぜ気づかなかったんだろう…。

```ruby
categories
  .project(Arel.star)
  .where(categories[:id].eq(id_10.as('id_10')))
  .to_sql
# => SELECT * FROM "categories"  WHERE "categories"."id" = (SELECT  "books"."category_id" FROM "books"  GROUP BY "books"."category_id" HAVING COUNT("books"."id") = 10 LIMIT 1) id_10" 
```

結局、全部 as を使えばいいんだということですね。
例は省略しますが、同様にSELECTの中にも書くことができました。

## 最後に
ここまでArelで書く必要があるのだろうか、生SQLでいいんじゃ、という気もするでしょう。
そのクエリで完結するならSQLで書いてもいいのですが、Railsで使うときはscopeにできるというメリットが大きいです。