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
Source
# 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
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.
Source
# 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
Loads the QueryBuilder
class for the connection of the given definition. If no specific adapter is found, the default QueryBuilder
class is returned.
Source
# 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
Initializes the instance by setting the relevant parameters
Public Instance Methods
Source
# 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
Actually builds the find parameters hash that should be used in the search_for named scope.
Source
# 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 if value =~ time_unit_regex("minutes?|hours?") 150 span = 1.minute 151 elsif value =~ time_unit_regex("days?|weeks?|months?|years?") || value =~ /\b(today|tomorrow|yesterday)\b/i 152 span = 1.day 153 else 154 tokens = DateTime._parse(value) 155 # find the smallest unit of time given in input and determine span for further adjustment of the search query 156 span = { 157 sec: 1.second, 158 min: 1.minute, 159 hour: 1.hour, 160 mday: 1.day, 161 mon: 1.month 162 }.find { |key, _| tokens[key] }&.last || 1.year 163 end 164 165 if [:eq, :ne].include?(operator) 166 # Instead of looking for an exact (non-)match, look for dates that 167 # fall inside/outside the range of timestamps of that day. 168 negate = (operator == :ne) ? 'NOT ' : '' 169 field_sql = field.to_sql(operator, &block) 170 return ["#{negate}(#{field_sql} >= ? AND #{field_sql} < ?)", timestamp, timestamp + span] 171 172 elsif span >= 1.day && operator == :gt 173 # Make sure timestamps on the given date are not included in the results 174 # by moving the date to the next day. 175 timestamp += span 176 operator = :gte 177 178 elsif span >= 1.day && operator == :lte 179 # Make sure the timestamps of the given date are included by moving the 180 # date to the next date. 181 timestamp += span 182 operator = :lt 183 end 184 end 185 186 # return the SQL test 187 ["#{field.to_sql(operator, &block)} #{sql_operator(operator, field)} ?", timestamp] 188 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.
Source
# 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
Source
# File lib/scoped_search/query_builder.rb 280 def find_has_many_through_association(field, through) 281 middle_table_association = nil 282 field.klass.reflect_on_all_associations(:has_many).each do |reflection| 283 class_name = reflection.options[:class_name].constantize.table_name if reflection.options[:class_name] 284 middle_table_association = reflection.name if class_name == through.to_s 285 middle_table_association = reflection.plural_name if reflection.plural_name == through.to_s 286 end 287 middle_table_association 288 end
Source
# File lib/scoped_search/query_builder.rb 301 def has_many_through_join(field) 302 many_class = field.definition.klass 303 connection = many_class.connection 304 sql = connection.quote_table_name(many_class.table_name) 305 join_reflections = nested_has_many(many_class, field.relation) 306 table_names = [[many_class.table_name, many_class.sti_name.tableize]] + join_reflections.map(&:table_name) 307 308 join_reflections.zip(table_names.zip(join_reflections.drop(1))).reduce(sql) do |acc, (reflection, (previous_table, next_reflection))| 309 fk1, pk1 = if reflection.respond_to?(:join_keys) 310 klass = reflection.method(:join_keys).arity == 1 ? [reflection.klass] : [] # ActiveRecord <5.2 workaround 311 reflection.join_keys(*klass).values # We are joining the tables "in reverse", so the PK and FK are swapped 312 else 313 [reflection.join_primary_key, reflection.join_foreign_key] #ActiveRecord 6.1 314 end 315 316 previous_table, sti_name = previous_table 317 # primary and foreign keys + optional conditions for the joins 318 join_condition = if with_polymorphism?(reflection) 319 field.reflection_conditions(definition.reflection_by_name(next_reflection.klass, sti_name || previous_table)) 320 else 321 '' 322 end 323 324 acc + <<-SQL 325 INNER JOIN #{connection.quote_table_name(reflection.table_name)} 326 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} 327 SQL 328 end 329 end
Source
# File lib/scoped_search/query_builder.rb 197 def map_value(field, value) 198 old_value = value 199 translator = field.value_translation 200 value = translator.call(value) if translator 201 raise ScopedSearch::QueryNotSupported, "Translation from any value to nil is not allowed, translated '#{old_value}'" if value.nil? 202 value 203 end
Source
# File lib/scoped_search/query_builder.rb 291 def nested_has_many(many_class, relation) 292 acc = [relation] 293 while (reflection = definition.reflection_by_name(many_class, relation)) 294 break if reflection.nil? || reflection.options[:through].nil? 295 relation = reflection.options[:through] 296 acc.unshift(relation) 297 end 298 acc.map { |relation| definition.reflection_by_name(many_class, relation) } 299 end
Walk the chain of has-many-throughs, collecting all tables we will need to join
Source
# 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
Source
# File lib/scoped_search/query_builder.rb 263 def preprocess_parameters(field, operator, value, &block) 264 values = if [:in, :notin].include?(operator) 265 value.split(',').map { |v| map_value(field, field.set? ? translate_value(field, v) : v.strip) } 266 elsif [:like, :unlike].include?(operator) 267 [(value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%')] 268 else 269 [map_value(field, field.offset ? value.to_i : value)] 270 end 271 values.each { |value| yield(:parameter, value) } 272 end
Source
# File lib/scoped_search/query_builder.rb 207 def set_test(field, operator,value, &block) 208 set_value = translate_value(field, value) 209 raise ScopedSearch::QueryNotSupported, "Operator '#{operator}' not supported for '#{field.field}'" unless [:eq,:ne].include?(operator) 210 negate = '' 211 if [true,false].include?(set_value) 212 negate = 'NOT ' if operator == :ne 213 if field.numerical? 214 operator = (set_value == true) ? :gt : :eq 215 set_value = 0 216 else 217 operator = (set_value == true) ? :ne : :eq 218 set_value = false 219 end 220 end 221 ["#{negate}(#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?)", set_value] 222 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.
Source
# 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
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.
Source
# File lib/scoped_search/query_builder.rb 232 def sql_test(field, operator, value, lhs, &block) # :yields: finder_option_type, value 233 return field.to_ext_method_sql(lhs, sql_operator(operator, field), value, &block) if field.virtual? 234 235 yield(:keyparameter, lhs.sub(/^.*\./,'')) if field.key_field 236 237 condition, *values = if field.temporal? 238 datetime_test(field, operator, value, &block) 239 elsif field.set? 240 set_test(field, operator, value, &block) 241 else 242 ["#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} #{value_placeholders(operator, value)}", value] 243 end 244 values.each { |value| preprocess_parameters(field, operator, value, &block) } 245 246 if field.relation && definition.reflection_by_name(field.definition.klass, field.relation).macro == :has_many 247 connection = field.definition.klass.connection 248 reflection = definition.reflection_by_name(field.definition.klass, field.relation) 249 primary_key_col = reflection.options[:primary_key] || field.definition.klass.primary_key 250 primary_key = "#{connection.quote_table_name(field.definition.klass.table_name)}.#{connection.quote_column_name(primary_key_col)}" 251 key, join_table = if reflection.options.has_key?(:through) 252 [primary_key, has_many_through_join(field)] 253 else 254 [connection.quote_column_name(field.reflection_keys(reflection)[1]), 255 connection.quote_table_name(field.klass.table_name)] 256 end 257 258 condition = "#{primary_key} IN (SELECT #{key} FROM #{join_table} WHERE #{condition} )" 259 end 260 condition 261 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.
Source
# 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
Returns a NOT (…) SQL fragment that negates the current AST
node’s children
Source
# File lib/scoped_search/query_builder.rb 191 def translate_value(field, value) 192 translated_value = field.complete_value[value.to_sym] 193 raise ScopedSearch::QueryNotSupported, "'#{field.field}' should be one of '#{field.complete_value.keys.join(', ')}', but the query was '#{value}'" if translated_value.nil? 194 translated_value 195 end
Validate the key name is in the set and translate the value to the set value.
Source
# File lib/scoped_search/query_builder.rb 274 def value_placeholders(operator, value) 275 return '?' unless [:in, :notin].include?(operator) 276 277 '(' + value.split(',').map { '?' }.join(',') + ')' 278 end
Source
# File lib/scoped_search/query_builder.rb 331 def with_polymorphism?(reflection) 332 as = reflection.options[:as] 333 return unless as 334 definition.reflection_by_name(reflection.klass, as).options[:polymorphic] 335 end
Private Instance Methods
Source
# File lib/scoped_search/query_builder.rb 339 def time_unit_regex(time_unit) 340 /\A\s*\d+\s+\b(?:#{time_unit})\b\s+\b(ago|from\s+now)\b\s*\z/i 341 end