find_by_sql を使う際のTips

読んでいたコードで面白いテクニックを使っていたのでメモ。
RailsActiveRecord のfind 系メソッドは優秀だと思うけど、自分でSQL を書きたい時があると思います。そこで、find_by_sql を使うわけですが、その際、大規模なSQL を書く場合のTips です。

  • 環境

ruby 1.8.7
rails 2.2.2

パラメータによってSQL 自体を変えたい場合

例えば、SQL が以下だったとします。

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}]) 
#=> []


問題なのな「:name => nil」の場合。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 の場合を含め、等価をあらわす演算子です。