どうもお久しぶりです。
morimotoです。
今日は複雑なActiveRecordの条件句を作成する際に（もしかしたら）楽になるのではないかと思いconditionを生成するクラスを作ってみました。
class ARCond
module OPERATION
EQUAL = '='
NOT_EQUAL = '<>'
IN = 'IN'
GREATER = '>'
GREATER_OR_EQUAL = '>='
LESS = '< '
LESS_OR_EQUAL = '<='
end
attr_reader :conditions, :parameters, :separator, :table_name
def initialize(separator="AND", table_name=nil)
@conditions = []
@parameters = []
@separator = separator
@table_name = table_name
end
# column = ?
def equal(column, value, table=nil)
add(column, value, OPERATION::EQUAL, table)
end
# column <> ?
def not_equal(column, value, table=nil)
add(column, value, OPERATION::NOT_EQUAL, table)
end
# column IN (?,?,?...)
def in(column, value, table=nil)
add(column, value, OPERATION::IN, table)
end
# column > ?
def greater(column, value, table=nil)
add(column, value, OPERATION::GREATER, table)
end
# column >= ?
def greater_or_equal(column, value, table=nil)
add(column, value, OPERATION::GREATER_OR_EQUAL, table)
end
# column < ?
def less(column, value, table=nil)
add(column, value, OPERATION::LESS, table)
end
# column <= ?
def less_or_equal(column, value, table=nil)
add(column, value, OPERATION::LESS_OR_EQUAL, table)
end
def << (other)
cond = nil
param = []
if other.kind_of? ARCond
c = other.build
cond = c.delete_at(0)
param = c
elsif other.kind_of? String
cond = other
elsif other.kind_of? Array
cond = other.delete_at(0)
param = other
end
unless cond.blank?
@conditions << cond
@parameters.concat(param)
end
return self.build
end
# [conditions, param, param, param,...]
def build
[join_conditions].concat(@parameters)
end
private
def join_conditions
sep = " #{@separator} "
"(#{@conditions.join(sep)})"
end
def column_fullname(column, table=nil)
unless column =~ /^[\w]+\.[\w]+$/
if table_name = table.blank? ? @table_name : table
return "#{table_name}.#{column}"
end
end
column
end
def add(column, value, operation, table=nil)
parts = [column_fullname(column, table), operation]
param = [value].flatten
parts << ((operation == OPERATION::IN) ? "(#{param.map{'?'}.join(',')})" : "?")
self << [parts.join(' ')].concat(param)
end
end
使い方の例）
# 基本スタイル
c1 = ARCond.new("AND")
c1.equal("col1", true)
# => ["(col1 = ?)", true]
c1.not_equal("col2", nil)
# => ["(col1 = ? AND col2 <> ?)", true, nil]
c1.in("col3", [31,32,33])
# => ["(col1 = ? AND col2 <> ? AND col3 IN (?,?,?))", true, nil, 31, 32, 33]
# 連結文字を変更する
c2 = ARCond.new("OR")
# 指定した連結文字で結合されていく
c2.greater("col4", 100)
# => ["(col4 > ?)", 100]
c2.less("col4", 200)
# => ["(col4 > ? OR col4 < ?)", 100, 200]
# 配列みたいに追加していく
c1 << c2
# => ["(col1 = ? AND col2 <> ? AND col3 IN (?,?,?) AND (col4 > ? OR col4 < ?))", true, nil, 31, 32, 33, 100, 200]
# 引数なしはANDで連結
c3 = ARCond.new()
# いつもどおりの配列の conditions もくっつけれる
c3 << ["col5 = ? AND col6 IN (?,?)", 11,22,33]
# => ["(col5 = ? AND col6 IN (?,?))", 11, 22, 33]
# 任意文字列もくっつけれる
c3 < < "col7 > col8"
# => ["(col5 = ? AND col6 IN (?,?) AND col7 > col8)", 11, 22, 33]
# テーブル名を指定する
c4 = ARCond.new("OR", "sample")
# カラム名の頭にテーブル名が付与される
c4.greater_or_equal("col9", 300)
# => ["(sample.col9 >= ?)", 300]
c4.less_or_equal("col9", 400)
# => ["(sample.col9 >= ? OR sample.col9 < = ?)", 300, 400]
# くっつけるときにテーブル名を指定する
c4.equal("col10", false, "banana")
# => ["(sample.col9 >= ? OR sample.col9 < = ? OR banana.col10 = ?)", 300, 400, false]
c3 << c4
# => ["(col5 = ? AND col6 IN (?,?) AND col7 > col8 AND (sample.col9 >= ? OR sample.col9 < = ? OR banana.col10 = ?))", 11, 22, 33, 300, 400, false]
c1 << c3
# => ["(col1 = ? AND col2 <> ? AND col3 IN (?,?,?) AND (col4 > ? OR col4 < ?) AND (col5 = ? AND col6 IN (?,?) AND col7 > col8 AND (sample.col9 >= ? OR sample.col9 < = ? OR banana.col10 = ?)))", true, nil, 31, 32, 33, 100, 200, 11, 22, 33, 300, 400, false]
# 最終的に使いたいタイミングで build してあげたらよい
c1.build
# => ["(col1 = ? AND col2 <> ? AND col3 IN (?,?,?) AND (col4 > ? OR col4 < ?) AND (col5 = ? AND col6 IN (?,?) AND col7 > col8 AND (sample.col9 >= ? OR sample.col9 < = ? OR banana.col10 = ?)))", true, nil, 31, 32, 33, 100, 200, 11, 22, 33, 300, 400, false]
作っておいてなんですが、テーブルが複数あったり、集計クエリが絡んできたりと、複雑になればなるほど自分でwhere句を書いたほうが早い気がします。
以上、morimoto でした。
