find_by_sql を使う際のTips

読んでいたコードで面白いテクニックを使っていたのでメモ。
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}]) 
#=> []

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

日本人は頑張りすぎなのか

今日昼頃テレビを見ていたらちょっと興味があるものがやっていた。「東大の研究室に所属するあるセルビアから来た人」の話。
日本の宗教に関して学んでいるという。「日本ではクリスマスを祝い、亡くなればお墓に入り等宗教がMix している」と言う。そして、そこが彼にとってとても興味のあることだと言ってた。

一年間の勉強で日本語を流暢に話す

彼はとても流暢に日本語を話し、オシム監督の会見時、通訳を務めたほどの人だ。セルビアにいた頃も日本語を学んでいたが、日本では慶応大で1年間日本語を学んだという。毎日16時間くらい日本語の勉強をしたとのことだ。
アナウンサーが、「よく1年で話せるようになりましたね。凄いです」って聞いたら、「いや、日本から奨学金も貰って1年も勉強したのに話せなかったら私は切腹ですよ（笑）」って言ってた。

セルビアから来た人達の集まり

その後、彼がセルビアから来た人達のパーティに参加するとのことで、その様子を取材していた。16人ほどのセルビア人のかたが来ていたが、皆流暢に日本語を話す。「まいうー」とかも言ってた（笑）
それだけでなく、英語も話す。何故英語を話せるかというと、彼らが日本語を勉強した頃、セルビアには「セルビア後-日本語辞書」はなかったのだと言う。なので、日本語を学ぶには、「セルビア語-英語」「英語-日本語」の順番で学ぶという。皆そうして勉強したのだ。

彼らから見た日本の国民性

アナウンサーが「日本の国民性はどう思いますか？」と質問すると、「頑張り過ぎ」、「今日できることは明日やらせて欲しい」と言ってた。
ここで疑問を覚えた。英語を媒介にして日本語を学び、日本で暮らしている彼らを見ると、とても努力しているという印象を受ける。その彼らが「頑張り過ぎ」というほど頑張っている日本人がそんなにいるのか。
彼らのの周りにはいるのかもしれない。が、日本人の僕からみて、日本人が頑張りすぎという印象はあまり思いつかない。
でも、僕自身同じような体験をしたことがある。イギリスに語学研修に言った時、「日本人はよく働く。そんなに働かなくてもいいんじゃないか」というようなことを言われた。

何故頑張りすぎと見えたのか

彼らのいう「頑張り過ぎ」の対象は、多分仕事のことだと思う。確かに、日本は残業が多い方だと思う。僕が語学研修にいった街では夕方くらいになると大抵の店はしまってしまうし、24H のコンビニなどなかなかない。どこだったかは忘れたが。有給が残っていると、残った分は企業が買い取らなければいけないという決まりがある国もあるという話も聞いたことがある。
でも、残業している大勢の人の中、彼らのように努力している人がそんなにいるとは思えない。どっちかというと、仕事に忙殺されている感じ。自分の仕事の質を高める勉強をしている人は少数だと思う。
残業が多いのは勉強してないからかな？勉強すれば同じ仕事をより速く片付けられるが、その時間を与えてもらえないということかな。
うーん。だんだん話がそれそうなのでこの辺で。色んな問題が絡んできてもやもやしてしまったけど、「日本人は頑張り過ぎ」っていう一言はとても興味深いものだった。