読んでいたコードで面白いテクニックを使っていたのでメモ。
Rails のActiveRecord のfind 系メソッドは優秀だと思うけど、自分でSQL を書きたい時があると思います。そこで、find_by_sql を使うわけですが、その際、大規模なSQL を書く場合のTips です。
- 環境
ruby 1.8.7
rails 2.2.2
パラメータによってSQL 自体を変えたい場合
例えば、SQL が以下だったとします。
- RDBMS はSQLite3
SELECT * from users where name = xxx;
これを扱う、find_by_sql が以下のような感じ。
User.find_by_sql(["select * from users where name = :name", {:name => 'bob'}]) #=> [#<User id: 1, name: "bob", created_at: "2008-11-23 16:26:27", updated_at: "2008-11-23 16:26:27">] User.find_by_sql(["select * from users where name = :name", {:name => nil}]) #=> []
上の問題を解決する3 つの方法
- 1. CASE 文やIF 文で対応
- 2. IFNULL で対応
- 3. ERB で対応
1. CASE 文やIF 文で対応
- SQLite ってIF 関数ないのかな? 仕方ないのでCASE で対応。
User.find_by_sql(["select * from users where case when :name NOTNULL then name = :name else 1 end", {:name => nil}]) #=> [#<User id: 1, name: "bob", created_at: "2008-11-23 16:26:27", updated_at: "2008-11-23 16:26:27">, #<User id: 2, name: "hane", created_at: "2008-11-23 17:27:46", updated_at: "2008-11-23 17:27:46">] User.find_by_sql(["select * from users where case when :name NOTNULL then name = :name else 1 end", {:name => 'bob'}]) #=> [#<User id: 1, name: "bob", created_at: "2008-11-23 16:26:27", updated_at: "2008-11-23 16:26:27">]
1 がかっこ悪い。長い。
2. IFNULL で対応
COALESCE と同じね。こっちの方が名前がわかりやすいのでこっち使う。
User.find_by_sql(["select * from users where name = IFNULL(:name, name)", {:name => nil}]) #=> [#<User id: 1, name: "bob", created_at: "2008-11-23 16:26:27", updated_at: "2008-11-23 16:26:27">, #<User id: 2, name: "hane", created_at: "2008-11-23 17:27:46", updated_at: "2008-11-23 17:27:46">] User.find_by_sql(["select * from users where name = IFNULL(:name, name)", {:name => 'bob'}]) #=> [#<User id: 1, name: "bob", created_at: "2008-11-23 16:26:27", updated_at: "2008-11-23 16:26:27">]
name = name は常に真。これはIN 句でも使える。(ここに関して誤りがありましたので、追記しました)
name IN IFNULL(:name, name)
でも、「:name => nil」だったらその部分のSQL いらないよな〜。
3. ERB を使う
ERB はHTML に使うだけじゃもったいない。便利。
require 'erb' class User < ActiveRecord::Base def self.optimized_find(params = { }) sql = <<-SQL SELECT * FROM users WHERE <% if params[:name] %> name = :name AND <% end %> 1 SQL find_by_sql([ERB.new(sql).result(binding), params]) end end User.optimized_find(:name => nil) #=> [#<User id: 1, name: "bob", created_at: "2008-11-23 16:26:27", updated_at: "2008-11-23 16:26:27">, #<User id: 2, name: "hane", created_at: "2008-11-23 17:27:46", updated_at: "2008-11-23 17:27:46">] User.optimized_find(:name => 'bob') #=> [#<User id: 1, name: "bob", created_at: "2008-11-23 16:26:27", updated_at: "2008-11-23 16:26:27">]
#{} 使うより見やすい。1 が復活しちゃったけど、find_by_sql で長いSQL を使う場合は使える。条件によってSQL 自体が小さくなるので、ログを見たりする時も余計なものがなくて見やすい。
少し手を加えると以下のような感じ。実際はもっと改良できるけど、Tips なのでこのくらいで。
- user.rb
require 'erb' class User < ActiveRecord::Base def self.optimized_find(parameters = { }) params = parameters.dup params.symbolize_keys! sql = <<-SQL SELECT * FROM users WHERE <% if params[:name] %> name = :name AND <% end %> 1 SQL keys = sql.scan(/:(\w+)/).flatten.uniq.map(&:to_sym) params.assert_valid_keys(keys) find_by_sql([ERB.new(sql).result(binding), params]) end end
- users.yml
bob: id: 1 name: bob tom: id: 2 name: tom
初期値としてid カラムがなかったけど、何か規約があったっけか?テストこけたので指定した。
- user_test.rb
require 'test_helper' class UserTest < ActiveSupport::TestCase fixtures :users test "optimized_find without arguments return all record" do assert_equal 2, User.optimized_find.size end test "optimized_find with arguments return record matched conditions" do result = User.optimized_find(:name => 'bob') assert_equal 1, result.size assert_equal 1, result.first.id end test "optimized_find with arguments have nil ignored" do assert_equal 2, User.optimized_find(:name => nil).size end test "optimized_find with unexist keys in sql rase argument error" do assert_raise(ArgumentError){ User.optimized_find(:hoge => 'bob') } end end
Rails 2.2 からテストは宣言っぽく書けるようになったのね。
追記
IN 句では使えませんでした。動作するのは「:name」がサイズ0か1の配列の時のみでした。2以上の場合は、IFNULL の引数エラーになり、COALESCE を使った場合は、一番最初の値しか考慮されません。
User.find_by_sql(["select * from users where name = IFNULL(:name, name)", {:name => ['bob', 'hane']}]) # => ActiveRecord::StatementInvalid: SQLite3::SQLException: wrong number of arguments to function IFNULL():... User.find_by_sql(["select * from users where name = COALESCE(:name, name)", {:name => ['bob', 'hane']}]) #=> [#<User id: 1, name: "bob", created_at: "2008-11-23 16:26:27", updated_at: "2008-11-23 16:26:27">] User.find_by_sql(["select * from users where name = COALESCE(:name, name)", {:name => ['hane', 'bob']}]) # => [#<User id: 2, name: "hane", created_at: "2008-11-23 17:27:46", updated_at: "2008-11-23 17:27:46">]
name = name が常に真になるのは、name がNULL でない時のみでした。
User.find(:all) #=> [#<User id: 1, name: "bob", created_at: "2008-11-23 16:26:27", updated_at: "2008-11-23 16:26:27">, #<User id: 2, name: nil, created_at: "2008-11-23 17:27:46", updated_at: "2008-11-23 17:27:46">] User.find_by_sql(["select * from users where name = COALESCE(:name, name)", {}]) #=> [#<User id: 1, name: "bob", created_at: "2008-11-23 16:26:27", updated_at: "2008-11-23 16:26:27">]
なので、NULL を許す属性に関しては2番目の以外の方法を使います。
MySQL の場合、比較演算子として「<=>」を使かうと2番目の方法も使うことができます。「<=>」はNULL の場合を含め、等価をあらわす演算子です。