読者です 読者をやめる 読者になる 読者になる

RailsでSQLのLike検索を行う

Ruby on Rails3でSQLのLike検索を行う場合、いろいろと参考になるサイトがあります。けれども、複数の列でLike検索を行いたい場合については見つかりませんでした。まぁそんな事はしないという話かもしれませんが…。プラグインも見つけられなかったので試しに作ってみました。

Like検索のクラスだけ取り出すのが面倒なので、moduleすべて載せます。

[sourcecode language="ruby"]

coding: utf-8

#

検索条件を生成するユーティリティモジュールです。

#

Author : ymanabe

Create Date: 11/11/03

Update Date: 11/11/03

#

== Summery

#

== Remarks

なし

# module ConditionUtil private

# 検索条件用のハッシュを生成します。 # # ==引数 # hash:検索条件用のハッシュ # key:ハッシュにセットするキー # value:ハッシュにセットする値 # ただし、範囲検索の場合は範囲検索のFROM # range_to:範囲検索のTO # # ==概要 # 検索条件のハッシュ値を生成しますが。 # def add_item_to_hash(hash, key, value, range_to = nil, table_name = nil)

if hash.nil?
  raise ArgumentError, 'param [hash] is nil.'
end

if key.nil?
  raise ArgumentError, ' param [key] is nil.'
end

if !key.kind_of?(Symbol) && !key.kind_of?(String)
  raise ArgumentError, 'param [key] is not kind of Symbol or String.'
end

if range_to.nil?
  if !key.nil? && !value.to_s.empty?

    if !table_name.nil? && !table_name.empty?
      hash_key = "#{table_name}.#{key}"
    else
      hash_key = key
    end

    if !hash.key? hash_key
      hash[hash_key] = value
      Rails.logger.debug "the hash add item. key:" + hash_key.to_s + " / value:" + hash[hash_key].to_s
    end
  end
else

  if !key.nil? && !value.to_s.empty?

    if !table_name.nil? && !table_name.empty?
      hash_key = "#{table_name}.#{value}"
    else
      hash_key = key
    end

    if !hash.key? hash_key
      hash[hash_key] = value .. range_to
      Rails.logger.debug "the hash add item. key:" + hash_key.to_s + " / value:" + hash[hash_key].to_s
    end
  end
end

end

# SqlLikeConditionクラス # # == 概要 # SQLのLike検索条件とハッシュ値に対応したクラスです。add_condition メソッドへ # 検索条件の構成情報をセットすることでLike検索を生成します。 class SqlLikeCondition

# 検索条件
@condition

# プレースフォルダと値のハッシュ値
@data_hash = Hash.new

def initialize
  @condition = ""
  @data_hash = Hash.new
end

# これ以降 public スコープとする
public

# 全文検索
MATCH_PATTERN_FULL = 1

# 前方一致検索
MATCH_PATTERN_FORWARD = 2

# 検索条件文字列
# add_conditions メソッドで生成した検索条件の文字列
def condition
  return @condition
end

# プレースフォルダと値のハッシュ
# add_conditions メソッドで生成した検索条件の文字列のプレースフォルダと値のセットを保持します
def data_hash
  return @data_hash
end

# 条件の追加
# == 引数
# table_name テーブル名
# col_name 対象となる列名
# value 対象となる値
# match_pattern 全文検索または前方一致検索を指定
def add_condition(table_name, col_name, value, match_pattern)

  if table_name.nil?
    raise ArgumentError, ' param [table_name] is nil.'
  end

  if table_name.empty?
    raise ArgumentError, ' param [table_name] is empty.'
  end

  if col_name.nil?
    raise ArgumentError, ' param [col_name] is nil.'
  end

  if !col_name.kind_of?(Symbol) && !col_name.kind_of?(String)
    raise ArgumentError, 'param [col_name] is not kind of Symbol or String.'
  end

  if match_pattern.nil?
    raise ArgumentError, 'param [match_pattern] is nil.'
  end

  # 1または2以外の場合はエラーとする
  if match_pattern != MATCH_PATTERN_FULL && match_pattern != MATCH_PATTERN_FORWARD
    raise ArgumentError, "param [match_patten:#{match_pattern}] is out of range."
  end

  # 検索条件を作成する(table_name.colname like %col_name%)
  @condition += ((@condition.empty?)? "" : " AND") + " #{table_name}.#{col_name.to_s} LIKE " + ":#{col_name.to_s}"

  if !@data_hash.key? col_name.intern
    @data_hash[col_name.intern] = ((match_pattern == MATCH_PATTERN_FULL) ? "%#{value}%" : "#{value}%")
    Rails.logger.debug @data_hash[col_name.intern]
  end
end

end end [/sourcecode]

次に使用方法です。

[sourcecode language="ruby"]

condition_hash = Hash.new
slc = SqlLikeCondition.new

if !params.nil?

  himoku_code = params[:himoku_code]
  tekiyo = params[:memo]
  pay_code = params[:payment_code]
  shiharai_saki = params[:shiharai_saki]

  shiharaibi_from = get_date('shiharaibi_from')
  shiharaibi_to = get_date('shiharaibi_to')

  # 費目IDが0の場合は指定なしのため選択しない(-.-)y-゜゜゜
  if himoku_code != "0"
    add_item_to_hash condition_hash, :himoku_code, himoku_code, nil, :himokus
  end

  # 適用は前方一致検索
  if !tekiyo.nil? && !tekiyo.empty?

    slc.add_condition "ledgers", :tekiyo, tekiyo, SqlLikeCondition::MATCH_PATTERN_FORWARD

    #add_item_like_to_hash condition, :tekiyo, tekiyo, 2
  end

  # 支払方法IDが0の場合は指定なしのため選択しない(゜゜;)エエッ
  if pay_code != "0"
    add_item_to_hash condition_hash, :pay_code, pay_code, nil, :payments
  end

  # 支払先は前方一致検索
  if !shiharai_saki.nil? && !shiharai_saki.empty?

    slc.add_condition :ledgers, :shiharai_saki, shiharai_saki, SqlLikeCondition::MATCH_PATTERN_FORWARD
    #add_item_like_to_hash condition, :shiharai_saki, shiharai_saki, 2
  end

  add_item_to_hash condition_hash, :spent_date, shiharaibi_from.to_s, shiharaibi_to.to_s
end

@ledgers = Ledger.joins('INNER JOIN himokus ON himokus.id = ledgers.himoku_id')
              .joins('INNER JOIN payments ON payments.id = ledgers.pay_id')
              .select('ledgers.*, himokus.himoku_code, himokus.himoku_name, payments.pay_name')
              .where(condition_hash).where(slc.condition,slc.data_hash).where("ledgers.del_flg = 0")
              .paginate(:page => params[:page],:per_page=>9)

[/sourcecode]

使用している内容は作っている最中の家計簿なので適当に想像してみてください。単純に扱っているのは、下記のwhereメソッドでハッシュまたはハッシュと検索条件文字列を渡すことで検索条件が生成されます。

[sourcecode language="ruby"] @ledgers = Ledger.joins('INNER JOIN himokus ON himokus.id = ledgers.himoku_id') .joins('INNER JOIN payments ON payments.id = ledgers.pay_id') .select('ledgers.*, himokus.himoku_code, himokus.himoku_name, payments.pay_name') .where(condition_hash).where(slc.condition,slc.data_hash).where("ledgers.del_flg = 0") .paginate(:page => params[:page],:per_page=>9) [/sourcecode]

もし、同じようなことで悩んでいる人がいたら参考になればと思います。また、内容について改善点や間違いなどwelcomです!

※ちなみにテストコードはこちらです。

[sourcecode language="ruby"]

coding: utf-8

require 'test_helper'

検索条件生成ユーティリティモジュールのテストクラス

Author : ymanabe

Create Date: 11/11/03

Update Date: 11/11/03

class ConditionUtilTest < ActiveSupport::TestCase include ConditionUtil

# add_itemのパラメータが正常に終了 test "add_item_to_hash normal" do

# 正常に処理が行われるパラメータを作成
result = Hash.new
key = 'key'
value = 'value'

# 処理の実行
add_item_to_hash result, key, value

assert_equal 1, result.size, 'Hashに保存された件数は1件'
assert_true result.has_key?(key), &quot;key:#{key}が含まれない&quot;
assert_true result.has_value?(value), &quot;値:#{value}が含まれない&quot;

# 同一のデータをセットする場合は何も行われない
add_item_to_hash result, key, value

assert_equal 1, result.size, 'Hashに保存された件数は1件'
assert_true result.has_key?(key), &quot;キー値:#{key}が含まれない&quot;
assert_true result.has_value?(value), &quot;値:#{value}が含まれない&quot;

# 異なるデータをセットする場合は正常に登録される
key = 'key2'
value = 'value2'
add_item_to_hash result, key, value

assert_equal 2, result.size, 'Hashに保存された件数は2件'
assert_true result.has_key?(key), &quot;キー値:#{key}が含まれない&quot;
assert_true result.has_value?(value), &quot;値:#{value}が含まれない&quot;

# 引数を2つ設定する場合は範囲指定文字列を値としてセットする
key = 'key3'
value = 'value_from'
value2 = 'value_to'
add_item_to_hash result, key, value, value2

assert_equal 3, result.size, 'Hashに保存された件数は3件'
assert_true result.has_key?(key), &quot;キー値:#{key}が含まれない&quot;

# 範囲指定オブジェクトを作成する
range_string = &quot;#{value}&quot;..&quot;#{value2}&quot;

assert_true result.has_value?(range_string), &quot;範囲:#{range_string}が含まれない&quot;

end

# add_itemのパラメータが正常に終了 test "add_item_to_hash normal with table_name" do

# 正常に処理が行われるパラメータを作成
result = Hash.new
key = 'key'

value = 'value'
table_name = &quot;table_name&quot;

hash_key = &quot;#{table_name}.#{key}&quot;

# 処理の実行
add_item_to_hash result, key, value, nil, table_name

assert_equal 1, result.size, 'Hashに保存された件数は1件'
assert_true result.has_key?(hash_key), &quot;キー値:#{hash_key}が含まれない&quot;
assert_true result.has_value?(value), &quot;値:#{value}が含まれない&quot;

# 同一のデータをセットする場合は何も行われない
add_item_to_hash result, key, value, nil, table_name

assert_equal 1, result.size, 'Hashに保存された件数は1件'
assert_true result.has_key?(hash_key), &quot;キー値:#{hash_key}が含まれない&quot;
assert_true result.has_value?(value), &quot;値:#{value}が含まれない&quot;

# 異なるデータをセットする場合は正常に登録される
key = 'key2'
value = 'value2'
hash_key2 = &quot;#{table_name}.#{key}&quot;
add_item_to_hash result, key, value, nil, table_name

assert_equal 2, result.size, 'Hashに保存された件数は2件'
assert_true result.has_key?(hash_key2), &quot;キー値:#{hash_key2}が含まれない&quot;
assert_true result.has_value?(value), &quot;値:#{value}が含まれない&quot;

# 引数を2つ設定する場合は範囲指定文字列を値としてセットする
key = 'key3'
value = 'value_from'
value2 = 'value_to'
hash_key3 = &quot;#{table_name}.#{key}&quot;
add_item_to_hash result, key, value, value2, table_name

assert_equal 3, result.size, 'Hashに保存された件数は3件'
assert_true result.has_key?(hash_key2), &quot;キー値:#{hash_key2}が含まれない&quot;

# 範囲指定オブジェクトを作成する
range_string = &quot;#{value}&quot;..&quot;#{value2}&quot;

assert_true result.has_value?(range_string), &quot;範囲:#{range_string}が含まれない&quot;

end

# add_itemのパラメータがnil test "add_item_to_hash param is nil" do

# 追加するハッシュがnilの場合は例外がスローされることを確認する
assert_raise(ArgumentError){add_item_to_hash(nil,nil,nil)}

# キーがnilの場合は例外がスローされることを確認する
assert_raise(ArgumentError){ add_item_to_hash(Hash.new,nil,nil) }

# キーがSymbolまたはStringでない場合は例外がスローされることを確認する
assert_raise(ArgumentError){ add_item_to_hash(Hash.new, 1, nil)}

end

test "SqlLikeCondition with string params one column and match pattern full" do

# インスタンスが生成できることを確認する
slc = SqlLikeCondition.new

assert_false slc.nil?, &quot;SqlLikeCondition is not instanced.&quot;

# 条件の生成
data_value_foo = 'foo_value'
data_col_name = 'fuga_col_name'
table_name = 'hoge_table'
slc.add_condition(table_name, data_col_name, data_value_foo, SqlLikeCondition::MATCH_PATTERN_FULL)

assert_not_nil(slc.data_hash, 'slc.data_hash is nil.')
assert_not_nil(slc.condition, 'slc.condition is nil.')
assert_not_empty(slc.condition, 'slc.condition is empty.')

# 検索条件の一致
EXP_CONDTION = &quot; hoge_table.fuga_col_name LIKE :#{data_col_name}&quot;
assert_equal EXP_CONDTION, slc.condition, 'slc.condition dose not make condition.'

# 検索条件の値の一致
assert_true slc.data_hash.key?(data_col_name.intern), &quot;data_hash dose not include key #{data_col_name}&quot;
assert_true slc.data_hash.value?(&quot;%#{data_value_foo}%&quot;), &quot;data_hash dose not include value #{data_value_foo}&quot;

end

test "SqlLikeCondition with string params one column and match pattern forward" do

# インスタンスが生成できることを確認する
slc = SqlLikeCondition.new

assert_false slc.nil?, &quot;SqlLikeCondition is not instanced.&quot;

# 条件の生成
data_value_foo = 'foo_value'
data_col_name = 'fuga_col_name'
table_name = 'hoge_table'
slc.add_condition(table_name, data_col_name, data_value_foo, SqlLikeCondition::MATCH_PATTERN_FORWARD)

assert_not_nil(slc.data_hash, 'slc.data_hash is nil.')
assert_not_nil(slc.condition, 'slc.condition is nil.')
assert_not_empty(slc.condition, 'slc.condition is empty.')

# 検索条件の一致
EXP_CONDTION = &quot; hoge_table.fuga_col_name LIKE :#{data_col_name}&quot;
assert_equal EXP_CONDTION, slc.condition, 'slc.condition dose not make condition.'

# 検索条件の値の一致
assert_true slc.data_hash.key?(data_col_name.intern), &quot;data_hash dose not include key #{data_value_foo}&quot;
assert_true slc.data_hash.value?(&quot;#{data_value_foo}%&quot;), &quot;data_hash dose not include value #{data_value_foo}&quot;

end

test "SqlLikeCondition with intern params one column" do

# インスタンスが生成できることを確認する
slc = SqlLikeCondition.new

assert_false slc.nil?, &quot;SqlLikeCondition is not instanced.&quot;

# 条件の生成
data_value_foo = 'foo_value'
data_col_name = 'fuga_col_name'.intern
table_name = 'hoge_table'
slc.add_condition(table_name, data_col_name, data_value_foo, SqlLikeCondition::MATCH_PATTERN_FULL)

assert_not_nil(slc.data_hash, 'slc.data_hash is nil.')
assert_not_nil(slc.condition, 'slc.condition is nil.')
assert_not_empty(slc.condition, 'slc.condition is empty.')

# 検索条件の一致
EXP_CONDTION = &quot; hoge_table.fuga_col_name LIKE :#{data_col_name}&quot;
assert_equal EXP_CONDTION, slc.condition, 'slc.condition dose not make condition.'

# 検索条件の値の一致
assert_true slc.data_hash.key?(data_col_name.intern), &quot;data_hash dose not include key #{data_value_foo}&quot;
assert_true slc.data_hash.value?(&quot;%#{data_value_foo}%&quot;), &quot;data_hash dose not include value #{data_value_foo}&quot;

end

test "SqlLikeCondition with intern params many columns" do

# インスタンスが生成できることを確認する
slc = SqlLikeCondition.new

assert_false slc.nil?, &quot;SqlLikeCondition is not instanced.&quot;

# ========================================================
# 条件の生成
# ========================================================

# 1つめのデータ
data_value_foo = 'foo_value'
data_col_name_foo = 'foo_col_name'.intern
table_name_foo = 'foo_table'

# 2つめのデータ
data_value_bar = 'bar_value'
data_col_name_bar = 'fuga_col_name'.intern
table_name_bar = 'hoge_table'

slc.add_condition(table_name_foo, data_col_name_foo, data_value_foo, SqlLikeCondition::MATCH_PATTERN_FULL)
slc.add_condition(table_name_bar, data_col_name_bar, data_value_bar, SqlLikeCondition::MATCH_PATTERN_FULL)

assert_not_nil(slc.data_hash, 'slc.data_hash is nil.')
assert_not_nil(slc.condition, 'slc.condition is nil.')
assert_not_empty(slc.condition, 'slc.condition is empty.')

# 検索条件の一致
EXP_CONDTION = &quot; #{table_name_foo}.#{data_col_name_foo} LIKE :#{data_col_name_foo} &quot;\
    &quot;AND #{table_name_bar}.#{data_col_name_bar} LIKE :#{data_col_name_bar}&quot;

assert_equal EXP_CONDTION, slc.condition, 'slc.condition dose not make condition.'

# 検索条件の値の一致
# 1つめのデータが存在する
assert_true slc.data_hash.key?(data_col_name_foo.intern), &quot;data_hash dose not include key #{data_value_foo}&quot;
assert_true slc.data_hash.value?(&quot;%#{data_value_foo}%&quot;), &quot;data_hash dose not include value #{data_value_foo}&quot;

# 2つめのデータが存在する
assert_true slc.data_hash.key?(data_col_name_bar.intern), &quot;data_hash dose not include key #{data_value_bar}&quot;
assert_true slc.data_hash.value?(&quot;%#{data_value_bar}%&quot;), &quot;data_hash dose not include value #{data_value_bar}&quot;

end end [/sourcecode]