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 = definition 42 @ast = ast 43 @definition.profile = profile 44 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 48 def build_find_params(options) 49 keyconditions = [] 50 keyparameters = [] 51 parameters = [] 52 includes = [] 53 joins = [] 54 55 # Build SQL WHERE clause using the AST 56 sql = @ast.to_sql(self, definition) do |notification, value| 57 58 # Handle the notifications encountered during the SQL generation: 59 # Store the parameters, includes, etc so that they can be added to 60 # the find-hash later on. 61 case notification 62 when :keycondition then keyconditions << value 63 when :keyparameter then keyparameters << value 64 when :parameter then parameters << value 65 when :include then includes << value 66 when :joins then joins << value 67 else raise ScopedSearch::QueryNotSupported, "Cannot handle #{notification.inspect}: #{value.inspect}" 68 end 69 end 70 # Build SQL ORDER BY clause 71 order = order_by(options[:order]) do |notification, value| 72 case notification 73 when :parameter then parameters << value 74 when :include then includes << value 75 when :joins then joins << value 76 else raise ScopedSearch::QueryNotSupported, "Cannot handle #{notification.inspect}: #{value.inspect}" 77 end 78 end 79 sql = (keyconditions + (sql.blank? ? [] : [sql]) ).map {|c| "(#{c})"}.join(" AND ") 80 # Build hash for ActiveRecord::Base#find for the named scope 81 find_attributes = {} 82 find_attributes[:conditions] = [sql] + keyparameters + parameters unless sql.blank? 83 find_attributes[:include] = includes.uniq unless includes.empty? 84 find_attributes[:joins] = joins.uniq unless joins.empty? 85 find_attributes[:order] = order unless order.nil? 86 87 # p find_attributes # Uncomment for debugging 88 return find_attributes 89 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 138 def datetime_test(field, operator, value, &block) # :yields: finder_option_type, value 139 140 # Parse the value as a date/time and ignore invalid timestamps 141 timestamp = definition.parse_temporal(value) 142 return [] unless timestamp 143 144 timestamp = timestamp.to_date if field.date? 145 # Check for the case that a date-only value is given as search keyword, 146 # but the field is of datetime type. Change the comparison to return 147 # more logical results. 148 if field.datetime? 149 span = 1.minute if(value =~ /\A\s*\d+\s+\bminutes?\b\s+\bago\b\s*\z/i) 150 span ||= (timestamp.day_fraction == 0) ? 1.day : 1.hour 151 if [:eq, :ne].include?(operator) 152 # Instead of looking for an exact (non-)match, look for dates that 153 # fall inside/outside the range of timestamps of that day. 154 negate = (operator == :ne) ? 'NOT ' : '' 155 field_sql = field.to_sql(operator, &block) 156 return ["#{negate}(#{field_sql} >= ? AND #{field_sql} < ?)", timestamp, timestamp + span] 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 # return the SQL test 173 ["#{field.to_sql(operator, &block)} #{sql_operator(operator, field)} ?", timestamp] 174 end
# File lib/scoped_search/query_builder.rb 91 def find_field_for_order_by(order, &block) 92 order ||= definition.default_order 93 return [nil, nil] if order.blank? 94 field_name, direction_name = order.to_s.split(/\s+/, 2) 95 field = definition.field_by_name(field_name) 96 raise ScopedSearch::QueryNotSupported, "the field '#{field_name}' in the order statement is not valid field for search" unless field 97 return field, direction_name 98 end
# File lib/scoped_search/query_builder.rb 266 def find_has_many_through_association(field, through) 267 middle_table_association = nil 268 field.klass.reflect_on_all_associations(:has_many).each do |reflection| 269 class_name = reflection.options[:class_name].constantize.table_name if reflection.options[:class_name] 270 middle_table_association = reflection.name if class_name == through.to_s 271 middle_table_association = reflection.plural_name if reflection.plural_name == through.to_s 272 end 273 middle_table_association 274 end
# File lib/scoped_search/query_builder.rb 287 def has_many_through_join(field) 288 many_class = field.definition.klass 289 connection = many_class.connection 290 sql = connection.quote_table_name(many_class.table_name) 291 join_reflections = nested_has_many(many_class, field.relation) 292 table_names = [[many_class.table_name, many_class.sti_name.tableize]] + join_reflections.map(&:table_name) 293 294 join_reflections.zip(table_names.zip(join_reflections.drop(1))).reduce(sql) do |acc, (reflection, (previous_table, next_reflection))| 295 fk1, pk1 = if reflection.respond_to?(:join_keys) 296 klass = reflection.method(:join_keys).arity == 1 ? [reflection.klass] : [] # ActiveRecord <5.2 workaround 297 reflection.join_keys(*klass).values # We are joining the tables "in reverse", so the PK and FK are swapped 298 else 299 [reflection.join_primary_key, reflection.join_foreign_key] #ActiveRecord 6.1 300 end 301 302 previous_table, sti_name = previous_table 303 # primary and foreign keys + optional conditions for the joins 304 join_condition = if with_polymorphism?(reflection) 305 field.reflection_conditions(definition.reflection_by_name(next_reflection.klass, sti_name || previous_table)) 306 else 307 '' 308 end 309 310 acc + <<-SQL 311 INNER JOIN #{connection.quote_table_name(reflection.table_name)} 312 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} 313 SQL 314 end 315 end
# File lib/scoped_search/query_builder.rb 183 def map_value(field, value) 184 old_value = value 185 translator = field.value_translation 186 value = translator.call(value) if translator 187 raise ScopedSearch::QueryNotSupported, "Translation from any value to nil is not allowed, translated '#{old_value}'" if value.nil? 188 value 189 end
Walk the chain of has-many-throughs, collecting all tables we will need to join
# File lib/scoped_search/query_builder.rb 277 def nested_has_many(many_class, relation) 278 acc = [relation] 279 while (reflection = definition.reflection_by_name(many_class, relation)) 280 break if reflection.nil? || reflection.options[:through].nil? 281 relation = reflection.options[:through] 282 acc.unshift(relation) 283 end 284 acc.map { |relation| definition.reflection_by_name(many_class, relation) } 285 end
# File lib/scoped_search/query_builder.rb 100 def order_by(order, &block) 101 field, direction_name = find_field_for_order_by(order, &block) 102 return nil if field.nil? 103 sql = field.to_sql(&block) 104 direction = (!direction_name.nil? && direction_name.downcase.eql?('desc')) ? " DESC" : " ASC" 105 return sql + direction 106 end
# File lib/scoped_search/query_builder.rb 249 def preprocess_parameters(field, operator, value, &block) 250 values = if [:in, :notin].include?(operator) 251 value.split(',').map { |v| map_value(field, field.set? ? translate_value(field, v) : v.strip) } 252 elsif [:like, :unlike].include?(operator) 253 [(value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%')] 254 else 255 [map_value(field, field.offset ? value.to_i : value)] 256 end 257 values.each { |value| yield(:parameter, value) } 258 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 193 def set_test(field, operator,value, &block) 194 set_value = translate_value(field, value) 195 raise ScopedSearch::QueryNotSupported, "Operator '#{operator}' not supported for '#{field.field}'" unless [:eq,:ne].include?(operator) 196 negate = '' 197 if [true,false].include?(set_value) 198 negate = 'NOT ' if operator == :ne 199 if field.numerical? 200 operator = (set_value == true) ? :gt : :eq 201 set_value = 0 202 else 203 operator = (set_value == true) ? :ne : :eq 204 set_value = false 205 end 206 end 207 ["#{negate}(#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?)", set_value] 208 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 117 def sql_operator(operator, field) 118 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? 119 SQL_OPERATORS[operator] 120 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 218 def sql_test(field, operator, value, lhs, &block) # :yields: finder_option_type, value 219 return field.to_ext_method_sql(lhs, sql_operator(operator, field), value, &block) if field.virtual? 220 221 yield(:keyparameter, lhs.sub(/^.*\./,'')) if field.key_field 222 223 condition, *values = if field.temporal? 224 datetime_test(field, operator, value, &block) 225 elsif field.set? 226 set_test(field, operator, value, &block) 227 else 228 ["#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} #{value_placeholders(operator, value)}", value] 229 end 230 values.each { |value| preprocess_parameters(field, operator, value, &block) } 231 232 if field.relation && definition.reflection_by_name(field.definition.klass, field.relation).macro == :has_many 233 connection = field.definition.klass.connection 234 reflection = definition.reflection_by_name(field.definition.klass, field.relation) 235 primary_key_col = reflection.options[:primary_key] || field.definition.klass.primary_key 236 primary_key = "#{connection.quote_table_name(field.definition.klass.table_name)}.#{connection.quote_column_name(primary_key_col)}" 237 key, join_table = if reflection.options.has_key?(:through) 238 [primary_key, has_many_through_join(field)] 239 else 240 [connection.quote_column_name(field.reflection_keys(reflection)[1]), 241 connection.quote_table_name(field.klass.table_name)] 242 end 243 244 condition = "#{primary_key} IN (SELECT #{key} FROM #{join_table} WHERE #{condition} )" 245 end 246 condition 247 end
Returns a NOT (…) SQL fragment that negates the current AST
node’s children
# File lib/scoped_search/query_builder.rb 123 def to_not_sql(rhs, definition, &block) 124 "NOT COALESCE(#{rhs.to_sql(self, definition, &block)}, 0)" 125 end
Validate the key name is in the set and translate the value to the set value.
# File lib/scoped_search/query_builder.rb 177 def translate_value(field, value) 178 translated_value = field.complete_value[value.to_sym] 179 raise ScopedSearch::QueryNotSupported, "'#{field.field}' should be one of '#{field.complete_value.keys.join(', ')}', but the query was '#{value}'" if translated_value.nil? 180 translated_value 181 end
# File lib/scoped_search/query_builder.rb 260 def value_placeholders(operator, value) 261 return '?' unless [:in, :notin].include?(operator) 262 263 '(' + value.split(',').map { '?' }.join(',') + ')' 264 end
# File lib/scoped_search/query_builder.rb 317 def with_polymorphism?(reflection) 318 as = reflection.options[:as] 319 return unless as 320 definition.reflection_by_name(reflection.klass, as).options[:polymorphic] 321 end