Rails Advent Calendar 8日目です。
Arel でサブクエリを組んでみようと思って、どうやるのかググるとこのページがヒット。いろんな例があってわかりやすいです。
Arelで色んなSQLを組み立ててみる - ryopekoの日記
サブクエリや型変換を伴うようなSQLの組み上げについては調査中です。
えー。
ということで調べたメモ。
基本
Arel::Table というクラスがあって、そこからメソッドをチェーンしていきます。
Arel::Table のオブジェクトは普通にnewして作ることができます。
books = Arel::Table.new(:books)
または、ActiveRecordのモデルからは arel_table というメソッドで取れます。
class Book < ActiveRecord::Base
...
end
books = Book.arel_table
books[:id]のように[]を使うと、 Arel::Attributes::Attribute というカラムを表すクラスのオブジェクトが取れます。これで条件を組み立てていきます。
例
ryopekoさんの記事からほぼそのまま引用させていただきます。
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 と同様に扱えます。
これがどこにも書いてなかった…。
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 を得ることができます。
なので、こういうミックスも可能。
内部でArel使ってるからできて当たり前ではありますが…
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条件の中でもサブクエリが使えますが、その場合は
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に( )が入ってない！
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
どうやればいいかよくわかりませんでした。
まあ、これはIN使ってもJOIN使っても書けるからいらないですね（笑）
同じく as を使って Arel::TableAlias にすればOKでした。なぜ気づかなかったんだろう…。
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にできるというメリットが大きいです。