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 264 def find_has_many_through_association(field, through) 265 middle_table_association = nil 266 field.klass.reflect_on_all_associations(:has_many).each do |reflection| 267 class_name = reflection.options[:class_name].constantize.table_name if reflection.options[:class_name] 268 middle_table_association = reflection.name if class_name == through.to_s 269 middle_table_association = reflection.plural_name if reflection.plural_name == through.to_s 270 end 271 middle_table_association 272 end
# File lib/scoped_search/query_builder.rb 285 def has_many_through_join(field) 286 many_class = field.definition.klass 287 connection = many_class.connection 288 sql = connection.quote_table_name(many_class.table_name) 289 join_reflections = nested_has_many(many_class, field.relation) 290 table_names = [many_class.table_name] + join_reflections.map(&:table_name) 291 292 join_reflections.zip(table_names.zip(join_reflections.drop(1))).reduce(sql) do |acc, (reflection, (previous_table, next_reflection))| 293 klass = reflection.method(:join_keys).arity == 1 ? [reflection.klass] : [] # ActiveRecord <5.2 workaround 294 fk1, pk1 = reflection.join_keys(*klass).values # We are joining the tables "in reverse", so the PK and FK are swapped 295 296 # primary and foreign keys + optional conditions for the joins 297 join_condition = if with_polymorphism?(reflection) 298 field.reflection_conditions(definition.reflection_by_name(next_reflection.klass, previous_table)) 299 else 300 '' 301 end 302 303 acc + <<-SQL 304 INNER JOIN #{connection.quote_table_name(reflection.table_name)} 305 ON #{connection.quote_table_name(previous_table)}.#{connection.quote_column_name(pk1)} = #{connection.quote_table_name(reflection.table_name)}.#{connection.quote_column_name(fk1)} #{join_condition} 306 SQL 307 end 308 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
Walk the chain of has-many-throughs, collecting all tables we will need to join
# File lib/scoped_search/query_builder.rb 275 def nested_has_many(many_class, relation) 276 acc = [relation] 277 while (reflection = definition.reflection_by_name(many_class, relation)) 278 break if reflection.nil? || reflection.options[:through].nil? 279 relation = reflection.options[:through] 280 acc.unshift(relation) 281 end 282 acc.map { |relation| definition.reflection_by_name(many_class, relation) } 283 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 247 def preprocess_parameters(field, operator, value, &block) 248 values = if [:in, :notin].include?(operator) 249 value.split(',').map { |v| map_value(field, field.set? ? translate_value(field, v) : v.strip) } 250 elsif [:like, :unlike].include?(operator) 251 [(value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%')] 252 else 253 [map_value(field, field.offset ? value.to_i : value)] 254 end 255 values.each { |value| yield(:parameter, value) } 256 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 reflection = definition.reflection_by_name(field.definition.klass, field.relation) 233 primary_key_col = reflection.options[:primary_key] || field.definition.klass.primary_key 234 primary_key = "#{connection.quote_table_name(field.definition.klass.table_name)}.#{connection.quote_column_name(primary_key_col)}" 235 key, join_table = if reflection.options.has_key?(:through) 236 [primary_key, has_many_through_join(field)] 237 else 238 [connection.quote_column_name(field.reflection_keys(reflection)[1]), 239 connection.quote_table_name(field.klass.table_name)] 240 end 241 242 condition = "#{primary_key} IN (SELECT #{key} FROM #{join_table} WHERE #{condition} )" 243 end 244 condition 245 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 258 def value_placeholders(operator, value) 259 return '?' unless [:in, :notin].include?(operator) 260 261 '(' + value.split(',').map { '?' }.join(',') + ')' 262 end
# File lib/scoped_search/query_builder.rb 310 def with_polymorphism?(reflection) 311 as = reflection.options[:as] 312 return unless as 313 definition.reflection_by_name(reflection.klass, as).options[:polymorphic] 314 end