module SQB::Filtering
Public Instance Methods
or(&block)
click to toggle source
Set that all conditions added in this block should be joined using OR rather than AND.
# File lib/sqb/filtering.rb, line 30 def or(&block) if @where_within_or.is_a?(Array) raise QueryError, "Cannot nest an or block within another or block" end @where_within_or ||= [] # Start by making an array within the OR block for this calling @where_within_or << [] # Execute the block. All queries to 'where' will be added to the last # array in the chain (created above) block.call ensure # Start work on a full array of SQL fragments for all OR queries @where_within_or_sql ||= [] # After each OR call, store up the SQL fragment for all where queries # executed within the block. if w = @where_within_or.pop @where_within_or_sql << w.map do |w| hash_to_sql(w) end.join(' OR ') end # When there are no fragments in the chain left, add it to the main # where chain for the query. if @where_within_or.empty? @where ||= [] @where << "(#{@where_within_or_sql.flatten.join(' OR ')})" @where_within_or_sql = nil end self end
where(hash = nil, &block)
click to toggle source
Add a condition to the query by providing a hash of keys and values.
@param hash [Hash] @return [Query]
# File lib/sqb/filtering.rb, line 10 def where(hash = nil, &block) if hash if @where_within_or && @where_within_or.last @where_within_or.last << hash else @where ||= [] @where << hash_to_sql(hash) end elsif block_given? dsl = WhereDSL.new block.call(dsl) where(dsl.hash) else raise QueryError, "Must provide a hash or a block to `where`" end self end
Private Instance Methods
condition(key, operator, value)
click to toggle source
# File lib/sqb/filtering.rb, line 83 def condition(key, operator, value) case operator when :equal if value.nil? "#{key} IS NULL" else "#{key} = #{value_escape(value)}" end when :not_equal if value.nil? "#{key} IS NOT NULL" else "#{key} != #{value_escape(value)}" end when :less_than, :lt "#{key} < #{value_escape(value)}" when :greater_than, :gt "#{key} > #{value_escape(value)}" when :less_than_or_equal_to, :lte "#{key} <= #{value_escape(value)}" when :greater_than_or_equal_to, :gte "#{key} >= #{value_escape(value)}" when :in, :not_in escaped_values = value.map { |v| value_escape(v) }.join(', ') if escaped_values.empty? # If there are no values to search from, don't find anything "1=0" else op = operator == :in ? "IN" : "NOT IN" "#{key} #{op} (#{escaped_values})" end when :like "#{key} LIKE #{value_escape(value)}" when :not_like "#{key} NOT LIKE #{value_escape(value)}" else raise InvalidOperatorError, "Invalid operator '#{operator}'" end end
hash_to_sql(hash, joiner = ' AND ')
click to toggle source
# File lib/sqb/filtering.rb, line 64 def hash_to_sql(hash, joiner = ' AND ') sql = hash.map do |key, value| with_table_and_column(key) do |table, column| key = escape_and_join(table, column) if value.is_a?(Array) condition(key, :in, value) elsif value.is_a?(Hash) sql = value.map do |operator, value| condition(key, operator, value) end sql.empty? ? "1=0" : sql.join(joiner) else condition(key, :equal, value) end end end.join(joiner) "(#{sql})" end