Help us understand the problem. What is going on with this article?

Arel でサブクエリ

More than 5 years have passed since last update.

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にできるというメリットが大きいです。

sonicgarden
「お客様に無駄遣いをさせない受託開発」と「習慣を変えるソフトウェアのサービス」に取り組んでいるソフトウェア企業
http://www.sonicgarden.jp
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