Class | ScopedSearch::QueryBuilder |
In: |
lib/scoped_search/query_builder.rb
|
Parent: | Object |
The QueryBuilder class builds an SQL query based on aquery string that is provided to the search_for named scope. It uses a SearchDefinition instance to shape the query.
SQL_OPERATORS | = | { :eq =>'=', :ne => '<>', :like => 'LIKE', :unlike => 'NOT LIKE', :gt => '>', :lt =>'<', :lte => '<=', :gte => '>=', :in => 'IN',:notin => 'NOT IN' } | A hash that maps the operators of the query language with the corresponding SQL operator. |
ast | [R] | |
definition | [R] |
Creates a find parameter hash that can be passed to ActiveRecord::Base#find, given a search definition and query string. This method is called from the search_for named scope.
This method will parse the query string and build an SQL query using the search query. It will return an empty hash if the search query is empty, in which case the scope call will simply return all records.
# File lib/scoped_search/query_builder.rb, line 17 17: def self.build_query(definition, *args) 18: query = args[0] ||='' 19: options = args[1] || {} 20: 21: query_builder_class = self.class_for(definition) 22: if query.kind_of?(ScopedSearch::QueryLanguage::AST::Node) 23: return query_builder_class.new(definition, query, options[:profile]).build_find_params(options) 24: elsif query.kind_of?(String) 25: return query_builder_class.new(definition, ScopedSearch::QueryLanguage::Compiler.parse(query), options[:profile]).build_find_params(options) 26: else 27: raise "Unsupported query object: #{query.inspect}!" 28: end 29: end
Loads the QueryBuilder class for the connection of the given definition. If no specific adapter is found, the default QueryBuilder class is returned.
# File lib/scoped_search/query_builder.rb, line 33 33: def self.class_for(definition) 34: self.const_get(definition.klass.connection.class.name.split('::').last) 35: rescue 36: self 37: end
Initializes the instance by setting the relevant parameters
# File lib/scoped_search/query_builder.rb, line 40 40: def initialize(definition, ast, profile) 41: @definition, @ast, @definition.profile = definition, ast, profile 42: end
Actually builds the find parameters hash that should be used in the search_for named scope.
# File lib/scoped_search/query_builder.rb, line 46 46: def build_find_params(options) 47: keyconditions = [] 48: keyparameters = [] 49: parameters = [] 50: includes = [] 51: joins = [] 52: 53: # Build SQL WHERE clause using the AST 54: sql = @ast.to_sql(self, definition) do |notification, value| 55: 56: # Handle the notifications encountered during the SQL generation: 57: # Store the parameters, includes, etc so that they can be added to 58: # the find-hash later on. 59: case notification 60: when :keycondition then keyconditions << value 61: when :keyparameter then keyparameters << value 62: when :parameter then parameters << value 63: when :include then includes << value 64: when :joins then joins << value 65: else raise ScopedSearch::QueryNotSupported, "Cannot handle #{notification.inspect}: #{value.inspect}" 66: end 67: end 68: # Build SQL ORDER BY clause 69: order = order_by(options[:order]) do |notification, value| 70: case notification 71: when :parameter then parameters << value 72: when :include then includes << value 73: when :joins then joins << value 74: else raise ScopedSearch::QueryNotSupported, "Cannot handle #{notification.inspect}: #{value.inspect}" 75: end 76: end 77: sql = (keyconditions + (sql.blank? ? [] : [sql]) ).map {|c| "(#{c})"}.join(" AND ") 78: # Build hash for ActiveRecord::Base#find for the named scope 79: find_attributes = {} 80: find_attributes[:conditions] = [sql] + keyparameters + parameters unless sql.blank? 81: find_attributes[:include] = includes.uniq unless includes.empty? 82: find_attributes[:joins] = joins.uniq unless joins.empty? 83: find_attributes[:order] = order unless order.nil? 84: 85: # p find_attributes # Uncomment for debugging 86: return find_attributes 87: end
Perform a comparison between a field and a Date(Time) value.
This function makes sure the date is valid and adjust the comparison in some cases to return more logical results.
This function needs a block that can be used to pass other information about the query (parameters that should be escaped, includes) to the query builder.
field: | The field to test. |
operator: | The operator used for comparison. |
value: | The value to compare the field with. |
# File lib/scoped_search/query_builder.rb, line 131 131: def datetime_test(field, operator, value, &block) # :yields: finder_option_type, value 132: 133: # Parse the value as a date/time and ignore invalid timestamps 134: timestamp = definition.parse_temporal(value) 135: return nil unless timestamp 136: 137: timestamp = timestamp.to_date if field.date? 138: # Check for the case that a date-only value is given as search keyword, 139: # but the field is of datetime type. Change the comparison to return 140: # more logical results. 141: if field.datetime? 142: span = 1.minute if(value =~ /\A\s*\d+\s+\bminutes?\b\s+\bago\b\s*\z/i) 143: span ||= (timestamp.day_fraction == 0) ? 1.day : 1.hour 144: if [:eq, :ne].include?(operator) 145: # Instead of looking for an exact (non-)match, look for dates that 146: # fall inside/outside the range of timestamps of that day. 147: yield(:parameter, timestamp) 148: yield(:parameter, timestamp + span) 149: negate = (operator == :ne) ? 'NOT ' : '' 150: field_sql = field.to_sql(operator, &block) 151: return "#{negate}(#{field_sql} >= ? AND #{field_sql} < ?)" 152: 153: elsif operator == :gt 154: # Make sure timestamps on the given date are not included in the results 155: # by moving the date to the next day. 156: timestamp += span 157: operator = :gte 158: 159: elsif operator == :lte 160: # Make sure the timestamps of the given date are included by moving the 161: # date to the next date. 162: timestamp += span 163: operator = :lt 164: end 165: end 166: 167: # Yield the timestamp and return the SQL test 168: yield(:parameter, timestamp) 169: "#{field.to_sql(operator, &block)} #{sql_operator(operator, field)} ?" 170: end
# File lib/scoped_search/query_builder.rb, line 89 89: def order_by(order, &block) 90: order ||= definition.default_order 91: return nil if order.blank? 92: field = definition.field_by_name(order.to_s.split(' ')[0]) 93: raise ScopedSearch::QueryNotSupported, "the field '#{order.to_s.split(' ')[0]}' in the order statement is not valid field for search" unless field 94: sql = field.to_sql(&block) 95: direction = (order.to_s.downcase.include?('desc')) ? " DESC" : " ASC" 96: order = sql + direction 97: 98: return order 99: end
A ‘set’ is group of possible values, for example a status might be "on", "off" or "unknown" and the database representation could be for example a numeric value. This method will validate the input and translate it into the database representation.
# File lib/scoped_search/query_builder.rb, line 181 181: def set_test(field, operator,value, &block) 182: set_value = translate_value(field, value) 183: raise ScopedSearch::QueryNotSupported, "Operator '#{operator}' not supported for '#{field.field}'" unless [:eq,:ne].include?(operator) 184: negate = '' 185: if [true,false].include?(set_value) 186: negate = 'NOT ' if operator == :ne 187: if field.numerical? 188: operator = (set_value == true) ? :gt : :eq 189: set_value = 0 190: else 191: operator = (set_value == true) ? :ne : :eq 192: set_value = false 193: end 194: end 195: yield(:parameter, set_value) 196: return "#{negate}(#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?)" 197: end
Return the SQL operator to use given an operator symbol and field definition.
By default, it will simply look up the correct SQL operator in the SQL_OPERATORS hash, but this can be overridden by a database adapter.
# File lib/scoped_search/query_builder.rb, line 110 110: def sql_operator(operator, field) 111: raise ScopedSearch::QueryNotSupported, "the operator '#{operator}' is not supported for field type '#{field.type}'" if [:like, :unlike].include?(operator) and !field.textual? 112: SQL_OPERATORS[operator] 113: end
Generates a simple SQL test expression, for a field and value using an operator.
This function needs a block that can be used to pass other information about the query (parameters that should be escaped, includes) to the query builder.
field: | The field to test. |
operator: | The operator used for comparison. |
value: | The value to compare the field with. |
# File lib/scoped_search/query_builder.rb, line 207 207: def sql_test(field, operator, value, lhs, &block) # :yields: finder_option_type, value 208: return field.to_ext_method_sql(lhs, sql_operator(operator, field), value, &block) if field.ext_method 209: 210: yield(:keyparameter, lhs.sub(/^.*\./,'')) if field.key_field 211: 212: if [:like, :unlike].include?(operator) 213: yield(:parameter, (value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%')) 214: return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?" 215: elsif [:in, :notin].include?(operator) 216: value.split(',').collect { |v| yield(:parameter, field.set? ? translate_value(field, v) : v.strip) } 217: value = value.split(',').collect { "?" }.join(",") 218: return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} (#{value})" 219: elsif field.temporal? 220: return datetime_test(field, operator, value, &block) 221: elsif field.set? 222: return set_test(field, operator, value, &block) 223: else 224: value = value.to_i if field.offset 225: yield(:parameter, value) 226: return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?" 227: end 228: end
Validate the key name is in the set and translate the value to the set value.
# File lib/scoped_search/query_builder.rb, line 173 173: def translate_value(field, value) 174: translated_value = field.complete_value[value.to_sym] 175: raise ScopedSearch::QueryNotSupported, "'#{field.field}' should be one of '#{field.complete_value.keys.join(', ')}', but the query was '#{value}'" if translated_value.nil? 176: translated_value 177: end