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 nil 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 yield(:parameter, timestamp) 153 yield(:parameter, timestamp + span) 154 negate = (operator == :ne) ? 'NOT ' : '' 155 field_sql = field.to_sql(operator, &block) 156 return "#{negate}(#{field_sql} >= ? AND #{field_sql} < ?)" 157 158 elsif operator == :gt 159 # Make sure timestamps on the given date are not included in the results 160 # by moving the date to the next day. 161 timestamp += span 162 operator = :gte 163 164 elsif operator == :lte 165 # Make sure the timestamps of the given date are included by moving the 166 # date to the next date. 167 timestamp += span 168 operator = :lt 169 end 170 end 171 172 # Yield the timestamp and return the SQL test 173 yield(:parameter, timestamp) 174 "#{field.to_sql(operator, &block)} #{sql_operator(operator, field)} ?" 175 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 184 def map_value(field, value) 185 old_value = value 186 translator = field.value_translation 187 value = translator.call(value) if translator 188 raise ScopedSearch::QueryNotSupported, "Translation from any value to nil is not allowed, translated '#{old_value}'" if value.nil? 189 value 190 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
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 194 def set_test(field, operator,value, &block) 195 set_value = translate_value(field, value) 196 raise ScopedSearch::QueryNotSupported, "Operator '#{operator}' not supported for '#{field.field}'" unless [:eq,:ne].include?(operator) 197 negate = '' 198 if [true,false].include?(set_value) 199 negate = 'NOT ' if operator == :ne 200 if field.numerical? 201 operator = (set_value == true) ? :gt : :eq 202 set_value = 0 203 else 204 operator = (set_value == true) ? :ne : :eq 205 set_value = false 206 end 207 end 208 yield(:parameter, set_value) 209 return "#{negate}(#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?)" 210 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 220 def sql_test(field, operator, value, lhs, &block) # :yields: finder_option_type, value 221 return field.to_ext_method_sql(lhs, sql_operator(operator, field), value, &block) if field.virtual? 222 223 yield(:keyparameter, lhs.sub(/^.*\./,'')) if field.key_field 224 225 if [:like, :unlike].include?(operator) 226 yield(:parameter, (value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%')) 227 return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?" 228 229 elsif [:in, :notin].include?(operator) 230 value.split(',').collect { |v| yield(:parameter, map_value(field, field.set? ? translate_value(field, v) : v.strip)) } 231 value = value.split(',').collect { "?" }.join(",") 232 return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} (#{value})" 233 234 elsif field.temporal? 235 return datetime_test(field, operator, value, &block) 236 237 elsif field.set? 238 return set_test(field, operator, value, &block) 239 240 elsif field.relation && definition.reflection_by_name(field.definition.klass, field.relation).macro == :has_many 241 value = value.to_i if field.offset 242 value = map_value(field, value) 243 yield(:parameter, value) 244 connection = field.definition.klass.connection 245 primary_key = "#{connection.quote_table_name(field.definition.klass.table_name)}.#{connection.quote_column_name(field.definition.klass.primary_key)}" 246 if definition.reflection_by_name(field.definition.klass, field.relation).options.has_key?(:through) 247 join = has_many_through_join(field) 248 return "#{primary_key} IN (SELECT #{primary_key} FROM #{join} WHERE #{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ? )" 249 else 250 foreign_key = connection.quote_column_name(field.reflection_keys(definition.reflection_by_name(field.definition.klass, field.relation))[1]) 251 return "#{primary_key} IN (SELECT #{foreign_key} FROM #{connection.quote_table_name(field.klass.table_name)} WHERE #{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ? )" 252 end 253 254 else 255 value = value.to_i if field.offset 256 value = map_value(field, value) 257 yield(:parameter, value) 258 return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?" 259 end 260 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 178 def translate_value(field, value) 179 translated_value = field.complete_value[value.to_sym] 180 raise ScopedSearch::QueryNotSupported, "'#{field.field}' should be one of '#{field.complete_value.keys.join(', ')}', but the query was '#{value}'" if translated_value.nil? 181 translated_value 182 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