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 252 def find_has_many_through_association(field, through) 253 middle_table_association = nil 254 field.klass.reflect_on_all_associations(:has_many).each do |reflection| 255 class_name = reflection.options[:class_name].constantize.table_name if reflection.options[:class_name] 256 middle_table_association = reflection.name if class_name == through.to_s 257 middle_table_association = reflection.plural_name if reflection.plural_name == through.to_s 258 end 259 middle_table_association 260 end
# File lib/scoped_search/query_builder.rb 262 def has_many_through_join(field) 263 many_class = field.definition.klass 264 through = definition.reflection_by_name(many_class, field.relation).options[:through] 265 connection = many_class.connection 266 267 # table names 268 endpoint_table_name = field.klass.table_name 269 many_table_name = many_class.table_name 270 middle_table_name = definition.reflection_by_name(many_class, through).klass.table_name 271 272 # primary and foreign keys + optional condition for the many to middle join 273 pk1, fk1 = field.reflection_keys(definition.reflection_by_name(many_class, through)) 274 condition1 = field.reflection_conditions(definition.reflection_by_name(field.klass, middle_table_name)) 275 276 # primary and foreign keys + optional condition for the endpoint to middle join 277 middle_table_association = find_has_many_through_association(field, through) || middle_table_name 278 pk2, fk2 = field.reflection_keys(definition.reflection_by_name(field.klass, middle_table_association)) 279 condition2 = field.reflection_conditions(definition.reflection_by_name(many_class, field.relation)) 280 281 <<-SQL 282 #{connection.quote_table_name(many_table_name)} 283 INNER JOIN #{connection.quote_table_name(middle_table_name)} 284 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)} #{condition1} 285 INNER JOIN #{connection.quote_table_name(endpoint_table_name)} 286 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} 287 SQL 288 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 186 def set_test(field, operator,value, &block) 187 set_value = translate_value(field, value) 188 raise ScopedSearch::QueryNotSupported, "Operator '#{operator}' not supported for '#{field.field}'" unless [:eq,:ne].include?(operator) 189 negate = '' 190 if [true,false].include?(set_value) 191 negate = 'NOT ' if operator == :ne 192 if field.numerical? 193 operator = (set_value == true) ? :gt : :eq 194 set_value = 0 195 else 196 operator = (set_value == true) ? :ne : :eq 197 set_value = false 198 end 199 end 200 yield(:parameter, set_value) 201 return "#{negate}(#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?)" 202 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 [: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 212 def sql_test(field, operator, value, lhs, &block) # :yields: finder_option_type, value 213 return field.to_ext_method_sql(lhs, sql_operator(operator, field), value, &block) if field.ext_method 214 215 yield(:keyparameter, lhs.sub(/^.*\./,'')) if field.key_field 216 217 if [:like, :unlike].include?(operator) 218 yield(:parameter, (value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%')) 219 return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?" 220 221 elsif [:in, :notin].include?(operator) 222 value.split(',').collect { |v| yield(:parameter, field.set? ? translate_value(field, v) : v.strip) } 223 value = value.split(',').collect { "?" }.join(",") 224 return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} (#{value})" 225 226 elsif field.temporal? 227 return datetime_test(field, operator, value, &block) 228 229 elsif field.set? 230 return set_test(field, operator, value, &block) 231 232 elsif field.relation && definition.reflection_by_name(field.definition.klass, field.relation).macro == :has_many 233 value = value.to_i if field.offset 234 yield(:parameter, value) 235 connection = field.definition.klass.connection 236 primary_key = "#{connection.quote_table_name(field.definition.klass.table_name)}.#{connection.quote_column_name(field.definition.klass.primary_key)}" 237 if definition.reflection_by_name(field.definition.klass, field.relation).options.has_key?(:through) 238 join = has_many_through_join(field) 239 return "#{primary_key} IN (SELECT #{primary_key} FROM #{join} WHERE #{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ? )" 240 else 241 foreign_key = connection.quote_column_name(field.reflection_keys(definition.reflection_by_name(field.definition.klass, field.relation))[1]) 242 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)} ? )" 243 end 244 245 else 246 value = value.to_i if field.offset 247 yield(:parameter, value) 248 return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?" 249 end 250 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