Railsでプログラムを書いるとSQLを直接記述する機会が意外と多いので、
なるべくRubyらしく書く方法がないか調べてみました。
ORなどかなり基本的な構文でもarel_tableを使う必要があるのですね。
というわけで、Arel::Table を使ってみました。
動作確認環境は以下の通りです。
- ruby: 1.9.3-p392
- Rails: 3.2.13
- arel: 3.0.2
- MySQL: 5.1.65
検索条件をORで繋げたい
基本的な構文ですがSQLを直接記述する場合が多いのではないでしょうか？
User.where("name = ? OR name = ?", "太郎", "花子").to_sql
#=> "SELECT `users`.* FROM `users` WHERE (name = '太郎' OR name = '花子')"
なるべくRubyで書こうとするとこうなります。
user_table = User.arel_table
User.where(user_table[:name].eq("太郎").or(user_table[:name].eq("花子"))).to_sql
#=> "SELECT `users`.* FROM `users` WHERE ((`users`.`name` = '太郎' OR `users`.`name` = '花子'))"
ActiveRecordに orメソッドを取り込んで欲しいというリクエストについて議論もされているようなので、
今後はもっと簡単に書けるようになるかもしれません。
Arel::Tableで使えるメソッド
サンプルコードに eq というメソッドが出て来ましたが、これは =(イコール)に変換されます。
他によく使うものでは不等号がありますが、これは以下のメソッドが対応しています。
user_table = User.arel_table
User.where(user_table[:age].lt(2)).to_sql
#=> "SELECT `users`.* FROM `users` WHERE (`users`.`age` < 60)"
User.where(user_table[:age].gt(2)).to_sql
#=> "SELECT `users`.* FROM `users` WHERE (`users`.`age` > 20)"
<= と >= はそれぞれ
User.where(user_table[:age].lteq(60)).to_sql
#=> "SELECT `users`.* FROM `users` WHERE (`users`.`age` <= 60)"
User.where(user_table[:age].gteq(20)).to_sql
#=> "SELECT `users`.* FROM `users` WHERE (`users`.`age` >= 20)"
他にもLIMIT、OFFSET、JOIN 等に独自のメソッドが存在します。
詳しくはGitHubのページ等を参照して下さい。
サブクエリを使う
Arel::Tableを利用すれば、サブクエリを使ったSQLを生成することができます。
以下はホワイトリストにまだ登録されていないユーザーを選択する場合を例にしたサンプルです。
ActiveRecord単体ではこのように書いていました。
white_listed_users = WhiteList.group(:user_id).pluck(:user_id)
#=> [1, 2, 3]
User.where("id NOT IN (?)", white_listed_users).to_sql
#=> "SELECT `users`.* FROM `users` WHERE (id NOT IN (1,2,3))"
Arel::Tableでサブクエリを利用した場合は以下のようになります。
user_table = User.arel_table
white_list_table = WhiteList.arel_table
User.where(user_table[:id].not_in(white_list_table.project(:user_id) ) ).to_sql
#=> "SELECT `users`.* FROM `users` WHERE (`users`.`id` NOT IN (SELECT user_id FROM `white_lists` ))"
ActiveRecord単体で生成する場合は2度クエリを発行していますが、サブクエリをつかえば一度発行するだけで済みます。
単純なクエリでは影響は少ないかもしれませんが、複雑なテーブル構成のデータの場合はパフォーマンスの向上が見込める場合もあるのではないでしょうか。
Arel::Table を使うメリット
コードの見た目がスッキリする
コード内でSQLを記述した場合
@admin = [true, false][rand(2)]
sql = <<-ENDSQL
SELECT *
FROM users
WHERE
#{ 'id IS NOT NULL' if @admin }
#{ '(id NOT IN (SELECT user_id FROM white_lists))' unless @admin }
ENDSQL
この例は短いですが、複雑なqueryではかなりの行をSQLの記述が占めてしまうのではないでしょうか。
また、パラメータによってqueryを書き換えたい場合 #{} を多用してしまうと見にくくなってしまいます。
スコープやクラスメソッドとして定義して再利用できる
query生成をメソッドに切りだすことで、Controller等から呼び出すことができるので
似たような検索を何度も行う場合はDRYに書くことができます。
class WhiteList < ActiveRecord::Base
...
def self.whitelisted_users
white_list_table.project(arel_table[:user_id])
end
end
class SampleController < ApplicationController
def show
user_table = User.arel_table
@users = User.where(user_table[:id].not_in(WhiteList.whitelisted_users))
end
end
Railsで検索を行うと検索結果が ActiveRecord::Relation のオブジェクトとして取得されます。
ActiveRecord::Relation には高機能なメソッドがいろいろ定義されていますが、query生成については
ActiveRecord::Relation#where_values が便利です。
このメソッドを使うとWHERE句をArelのオブジェクトとして取得できるので、
複数のscopeをAND やOR で連結したり
検索条件を追加したりといったことができるようになります。
class User < ActiveRecord::Base
scope :teenage, where(arel_table[:age].gteq(13).and(arel_table[:age].lteq(19)))
scope :senior, where(arel_table[:age].gt(60))
end
class SampleController < ApplicationController
def show
# 13歳から19歳の間もしくは61歳以上
teen_or_senior = User.teenage.senior.where_values.inject(:or)
@teen_or_senior = User.where(teen_or_senior)
@teen_or_senior.to_sql
#=> "SELECT `users`.* FROM `users` WHERE ((`users`.`age` >= 13 AND `users`.`age` <= 19 OR `users`.`age` > 60))"
# 60歳も含むという条件を後から追加
senior = User.senior.where_values.first
@senior = User.where(senior.or(User.arel_table[:age].eq(60)))
@senior.to_sql
#=> "SELECT `users`.* FROM `users` WHERE ((`users`.`age` < 60 OR `users`.`age` = 60))"
end
end
成人 未成年 男性 女性 etc... よく使う検索条件をscopeとして定義しておいて、
複数のscopeを組み合わせて複雑なqueryを作り上げるという使い方ができそうですね。
RDBMSが変わっても同じように使える
PostgreSQL 9.2.2で動作を確認しました。
MySQLと PostgreSQL ではboolean型のデータが違ったり
ORDER BY句でランダムに並べ替える際に使う関数名が違ったりと、
RDBMSによって動くqueryと動かないqueryがあるため、
生のSQLを使っているとサーバ移転等で環境が変わった際にソースコードの修正が必要になる場合があります。
しかし、Arelを適切に使っていればそういった変更が不要になるかもしれません。
Rails4でも動きます
そろそろRails4の正式版がリリースされますね。
せっかくなので、Ruby2.0 Rails4 の環境で動作するか確認してみました。
動作確認環境
- ruby: 2.0.0p195
- Rails: 4.0.0.rc1
- arel: 4.0.0
- MySQL: 5.1.65
例に上げたような単純なqueryであれば問題なく動作するようです。
scopeを利用した場合の例だけは少し問題があります。
そのままでも動作はするのですが、DEPRECATION WARNING が出てしまいます。
scopeの書き方が変わったようなので、指示の通りに書き換えます。
# DEPRECATION WARNING: Using #scope without passing a callable object is deprecated. For example `scope :red, where(color: 'red')` should be changed to `scope :red, -> { where(color: 'red') }`. There are numerous gotchas in the former usage and it makes the implementation more complicated and buggy. (If you prefer, you can just define a class method named `self.red`.).
class User < ActiveRecord::Base
scope :teenage, -> { where(arel_table[:age].gteq(13).and(arel_table[:age].lteq(19))) }
scope :senior, -> { where(arel_table[:age].gt(60)) }
end
これで警告も出なくなるはずです。
以上、簡単な例でしたがArelの使い方をまとめてみました。
便利なのでどんどん使いましょうとは言い難いですが、Arelのメソッドはなんだかかっこいいですね。