class ScopedSearch::QueryBuilder
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.
Constants
- SQL_OPERATORS
A hash that maps the operators of the query language with the corresponding SQL operator.
Attributes
Public Class Methods
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 17 def self.build_query(definition, query, options = {}) 18 query_builder_class = self.class_for(definition) 19 if query.kind_of?(ScopedSearch::QueryLanguage::AST::Node) 20 return query_builder_class.new(definition, query, options[:profile]).build_find_params(options) 21 elsif query.kind_of?(String) 22 return query_builder_class.new(definition, ScopedSearch::QueryLanguage::Compiler.parse(query), options[:profile]).build_find_params(options) 23 else 24 raise ArgumentError, "Unsupported query object: #{query.inspect}!" 25 end 26 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 30 def self.class_for(definition) 31 case definition.klass.connection.class.name.split('::').last 32 when /postgresql/i 33 PostgreSQLAdapter 34 else 35 self 36 end 37 end
Initializes the instance by setting the relevant parameters
# File lib/scoped_search/query_builder.rb 40 def initialize(definition, ast, profile) 41 @definition, @ast, @definition.profile = definition, ast, profile 42 end
Public Instance Methods
Actually builds the find parameters hash that should be used in the search_for named scope.
# File lib/scoped_search/query_builder.rb 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 136 def datetime_test(field, operator, value, &block) # :yields: finder_option_type, value 137 138 # Parse the value as a date/time and ignore invalid timestamps 139 timestamp = definition.parse_temporal(value) 140 return [] unless timestamp 141 142 timestamp = timestamp.to_date if field.date? 143 # Check for the case that a date-only value is given as search keyword, 144 # but the field is of datetime type. Change the comparison to return 145 # more logical results. 146 if field.datetime? 147 span = 1.minute if(value =~ /\A\s*\d+\s+\bminutes?\b\s+\bago\b\s*\z/i) 148 span ||= (timestamp.day_fraction == 0) ? 1.day : 1.hour 149 if [:eq, :ne].include?(operator) 150 # Instead of looking for an exact (non-)match, look for dates that 151 # fall inside/outside the range of timestamps of that day. 152 negate = (operator == :ne) ? 'NOT ' : '' 153 field_sql = field.to_sql(operator, &block) 154 return ["#{negate}(#{field_sql} >= ? AND #{field_sql} < ?)", timestamp, timestamp + span] 155 156 elsif operator == :gt 157 # Make sure timestamps on the given date are not included in the results 158 # by moving the date to the next day. 159 timestamp += span 160 operator = :gte 161 162 elsif operator == :lte 163 # Make sure the timestamps of the given date are included by moving the 164 # date to the next date. 165 timestamp += span 166 operator = :lt 167 end 168 end 169 170 # return the SQL test 171 ["#{field.to_sql(operator, &block)} #{sql_operator(operator, field)} ?", timestamp] 172 end
# File lib/scoped_search/query_builder.rb 89 def find_field_for_order_by(order, &block) 90 order ||= definition.default_order 91 return [nil, nil] if order.blank? 92 field_name, direction_name = order.to_s.split(/\s+/, 2) 93 field = definition.field_by_name(field_name) 94 raise ScopedSearch::QueryNotSupported, "the field '#{field_name}' in the order statement is not valid field for search" unless field 95 return field, direction_name 96 end
# File lib/scoped_search/query_builder.rb 262 def find_has_many_through_association(field, through) 263 middle_table_association = nil 264 field.klass.reflect_on_all_associations(:has_many).each do |reflection| 265 class_name = reflection.options[:class_name].constantize.table_name if reflection.options[:class_name] 266 middle_table_association = reflection.name if class_name == through.to_s 267 middle_table_association = reflection.plural_name if reflection.plural_name == through.to_s 268 end 269 middle_table_association 270 end
# File lib/scoped_search/query_builder.rb 272 def has_many_through_join(field) 273 many_class = field.definition.klass 274 through = definition.reflection_by_name(many_class, field.relation).options[:through] 275 through_class = definition.reflection_by_name(many_class, through).klass 276 277 connection = many_class.connection 278 279 # table names 280 endpoint_table_name = field.klass.table_name 281 many_table_name = many_class.table_name 282 middle_table_name = through_class.table_name 283 284 # primary and foreign keys + optional conditions for the joins 285 pk1, fk1 = field.reflection_keys(definition.reflection_by_name(many_class, through)) 286 condition_many_to_middle = if with_polymorphism?(many_class, field.klass, through, through_class) 287 field.reflection_conditions(definition.reflection_by_name(field.klass, many_table_name)) 288 else 289 '' 290 end 291 condition_middle_to_end = field.reflection_conditions(definition.reflection_by_name(field.klass, middle_table_name)) 292 293 # primary and foreign keys + optional condition for the endpoint to middle join 294 middle_table_association = find_has_many_through_association(field, through) || middle_table_name 295 pk2, fk2 = field.reflection_keys(definition.reflection_by_name(field.klass, middle_table_association)) 296 condition2 = field.reflection_conditions(definition.reflection_by_name(many_class, field.relation)) 297 298 <<-SQL 299 #{connection.quote_table_name(many_table_name)} 300 INNER JOIN #{connection.quote_table_name(middle_table_name)} 301 ON #{connection.quote_table_name(many_table_name)}.#{connection.quote_column_name(pk1)} = #{connection.quote_table_name(middle_table_name)}.#{connection.quote_column_name(fk1)} #{condition_many_to_middle} #{condition_middle_to_end} 302 INNER JOIN #{connection.quote_table_name(endpoint_table_name)} 303 ON #{connection.quote_table_name(middle_table_name)}.#{connection.quote_column_name(fk2)} = #{connection.quote_table_name(endpoint_table_name)}.#{connection.quote_column_name(pk2)} #{condition2} 304 SQL 305 end
# File lib/scoped_search/query_builder.rb 181 def map_value(field, value) 182 old_value = value 183 translator = field.value_translation 184 value = translator.call(value) if translator 185 raise ScopedSearch::QueryNotSupported, "Translation from any value to nil is not allowed, translated '#{old_value}'" if value.nil? 186 value 187 end
# File lib/scoped_search/query_builder.rb 98 def order_by(order, &block) 99 field, direction_name = find_field_for_order_by(order, &block) 100 return nil if field.nil? 101 sql = field.to_sql(&block) 102 direction = (!direction_name.nil? && direction_name.downcase.eql?('desc')) ? " DESC" : " ASC" 103 return sql + direction 104 end
# File lib/scoped_search/query_builder.rb 245 def preprocess_parameters(field, operator, value, &block) 246 values = if [:in, :notin].include?(operator) 247 value.split(',').map { |v| map_value(field, field.set? ? translate_value(field, v) : v.strip) } 248 elsif [:like, :unlike].include?(operator) 249 [(value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%')] 250 else 251 [map_value(field, field.offset ? value.to_i : value)] 252 end 253 values.each { |value| yield(:parameter, value) } 254 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 191 def set_test(field, operator,value, &block) 192 set_value = translate_value(field, value) 193 raise ScopedSearch::QueryNotSupported, "Operator '#{operator}' not supported for '#{field.field}'" unless [:eq,:ne].include?(operator) 194 negate = '' 195 if [true,false].include?(set_value) 196 negate = 'NOT ' if operator == :ne 197 if field.numerical? 198 operator = (set_value == true) ? :gt : :eq 199 set_value = 0 200 else 201 operator = (set_value == true) ? :ne : :eq 202 set_value = false 203 end 204 end 205 ["#{negate}(#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?)", set_value] 206 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 115 def sql_operator(operator, field) 116 raise ScopedSearch::QueryNotSupported, "the operator '#{operator}' is not supported for field type '#{field.type}'" if !field.virtual? and [:like, :unlike].include?(operator) and !field.textual? 117 SQL_OPERATORS[operator] 118 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 216 def sql_test(field, operator, value, lhs, &block) # :yields: finder_option_type, value 217 return field.to_ext_method_sql(lhs, sql_operator(operator, field), value, &block) if field.virtual? 218 219 yield(:keyparameter, lhs.sub(/^.*\./,'')) if field.key_field 220 221 condition, *values = if field.temporal? 222 datetime_test(field, operator, value, &block) 223 elsif field.set? 224 set_test(field, operator, value, &block) 225 else 226 ["#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} #{value_placeholders(operator, value)}", value] 227 end 228 values.each { |value| preprocess_parameters(field, operator, value, &block) } 229 230 if field.relation && definition.reflection_by_name(field.definition.klass, field.relation).macro == :has_many 231 connection = field.definition.klass.connection 232 primary_key = "#{connection.quote_table_name(field.definition.klass.table_name)}.#{connection.quote_column_name(field.definition.klass.primary_key)}" 233 key, join_table = if definition.reflection_by_name(field.definition.klass, field.relation).options.has_key?(:through) 234 [primary_key, has_many_through_join(field)] 235 else 236 [connection.quote_column_name(field.reflection_keys(definition.reflection_by_name(field.definition.klass, field.relation))[1]), 237 connection.quote_table_name(field.klass.table_name)] 238 end 239 240 condition = "#{primary_key} IN (SELECT #{key} FROM #{join_table} WHERE #{condition} )" 241 end 242 condition 243 end
Returns a NOT (…) SQL fragment that negates the current AST
node's children
# File lib/scoped_search/query_builder.rb 121 def to_not_sql(rhs, definition, &block) 122 "NOT COALESCE(#{rhs.to_sql(self, definition, &block)}, 0)" 123 end
Validate the key name is in the set and translate the value to the set value.
# File lib/scoped_search/query_builder.rb 175 def translate_value(field, value) 176 translated_value = field.complete_value[value.to_sym] 177 raise ScopedSearch::QueryNotSupported, "'#{field.field}' should be one of '#{field.complete_value.keys.join(', ')}', but the query was '#{value}'" if translated_value.nil? 178 translated_value 179 end
# File lib/scoped_search/query_builder.rb 256 def value_placeholders(operator, value) 257 return '?' unless [:in, :notin].include?(operator) 258 259 '(' + value.split(',').map { '?' }.join(',') + ')' 260 end
# File lib/scoped_search/query_builder.rb 307 def with_polymorphism?(many_class, endpoint_class, through, through_class) 308 reflections = [definition.reflection_by_name(endpoint_class, through), definition.reflection_by_name(many_class, through)].compact 309 as = reflections.map(&:options).compact.map { |opt| opt[:as] }.compact 310 return false if as.empty? 311 definition.reflection_by_name(through_class, as.first).options[:polymorphic] 312 end