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, line 17 def self.build_query(definition, query, options = {}) query_builder_class = self.class_for(definition) if query.kind_of?(ScopedSearch::QueryLanguage::AST::Node) return query_builder_class.new(definition, query, options[:profile]).build_find_params(options) elsif query.kind_of?(String) return query_builder_class.new(definition, ScopedSearch::QueryLanguage::Compiler.parse(query), options[:profile]).build_find_params(options) else raise ArgumentError, "Unsupported query object: #{query.inspect}!" end 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, line 30 def self.class_for(definition) case definition.klass.connection.class.name.split('::').last when /postgresql/i PostgreSQLAdapter else self end end
Initializes the instance by setting the relevant parameters
# File lib/scoped_search/query_builder.rb, line 40 def initialize(definition, ast, profile) @definition, @ast, @definition.profile = definition, ast, profile 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, line 46 def build_find_params(options) keyconditions = [] keyparameters = [] parameters = [] includes = [] joins = [] # Build SQL WHERE clause using the AST sql = @ast.to_sql(self, definition) do |notification, value| # Handle the notifications encountered during the SQL generation: # Store the parameters, includes, etc so that they can be added to # the find-hash later on. case notification when :keycondition then keyconditions << value when :keyparameter then keyparameters << value when :parameter then parameters << value when :include then includes << value when :joins then joins << value else raise ScopedSearch::QueryNotSupported, "Cannot handle #{notification.inspect}: #{value.inspect}" end end # Build SQL ORDER BY clause order = order_by(options[:order]) do |notification, value| case notification when :parameter then parameters << value when :include then includes << value when :joins then joins << value else raise ScopedSearch::QueryNotSupported, "Cannot handle #{notification.inspect}: #{value.inspect}" end end sql = (keyconditions + (sql.blank? ? [] : [sql]) ).map {|c| "(#{c})"}.join(" AND ") # Build hash for ActiveRecord::Base#find for the named scope find_attributes = {} find_attributes[:conditions] = [sql] + keyparameters + parameters unless sql.blank? find_attributes[:include] = includes.uniq unless includes.empty? find_attributes[:joins] = joins.uniq unless joins.empty? find_attributes[:order] = order unless order.nil? # p find_attributes # Uncomment for debugging return find_attributes 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, line 136 def datetime_test(field, operator, value, &block) # :yields: finder_option_type, value # Parse the value as a date/time and ignore invalid timestamps timestamp = definition.parse_temporal(value) return [] unless timestamp timestamp = timestamp.to_date if field.date? # Check for the case that a date-only value is given as search keyword, # but the field is of datetime type. Change the comparison to return # more logical results. if field.datetime? span = 1.minute if(value =~ /\A\s*\d+\s+\bminutes?\b\s+\bago\b\s*\z/i) span ||= (timestamp.day_fraction == 0) ? 1.day : 1.hour if [:eq, :ne].include?(operator) # Instead of looking for an exact (non-)match, look for dates that # fall inside/outside the range of timestamps of that day. negate = (operator == :ne) ? 'NOT ' : '' field_sql = field.to_sql(operator, &block) return ["#{negate}(#{field_sql} >= ? AND #{field_sql} < ?)", timestamp, timestamp + span] elsif operator == :gt # Make sure timestamps on the given date are not included in the results # by moving the date to the next day. timestamp += span operator = :gte elsif operator == :lte # Make sure the timestamps of the given date are included by moving the # date to the next date. timestamp += span operator = :lt end end # return the SQL test ["#{field.to_sql(operator, &block)} #{sql_operator(operator, field)} ?", timestamp] end
# File lib/scoped_search/query_builder.rb, line 89 def find_field_for_order_by(order, &block) order ||= definition.default_order return [nil, nil] if order.blank? field_name, direction_name = order.to_s.split(/\s+/, 2) field = definition.field_by_name(field_name) raise ScopedSearch::QueryNotSupported, "the field '#{field_name}' in the order statement is not valid field for search" unless field return field, direction_name end
# File lib/scoped_search/query_builder.rb, line 264 def find_has_many_through_association(field, through) middle_table_association = nil field.klass.reflect_on_all_associations(:has_many).each do |reflection| class_name = reflection.options[:class_name].constantize.table_name if reflection.options[:class_name] middle_table_association = reflection.name if class_name == through.to_s middle_table_association = reflection.plural_name if reflection.plural_name == through.to_s end middle_table_association end
# File lib/scoped_search/query_builder.rb, line 285 def has_many_through_join(field) many_class = field.definition.klass connection = many_class.connection sql = connection.quote_table_name(many_class.table_name) join_reflections = nested_has_many(many_class, field.relation) table_names = [many_class.table_name] + join_reflections.map(&:table_name) join_reflections.zip(table_names.zip(join_reflections.drop(1))).reduce(sql) do |acc, (reflection, (previous_table, next_reflection))| klass = reflection.method(:join_keys).arity == 1 ? [reflection.klass] : [] # ActiveRecord <5.2 workaround fk1, pk1 = reflection.join_keys(*klass).values # We are joining the tables "in reverse", so the PK and FK are swapped # primary and foreign keys + optional conditions for the joins join_condition = if with_polymorphism?(reflection) field.reflection_conditions(definition.reflection_by_name(next_reflection.klass, previous_table)) else '' end acc + <<-SQL INNER JOIN #{connection.quote_table_name(reflection.table_name)} 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} SQL end end
# File lib/scoped_search/query_builder.rb, line 181 def map_value(field, value) old_value = value translator = field.value_translation value = translator.call(value) if translator raise ScopedSearch::QueryNotSupported, "Translation from any value to nil is not allowed, translated '#{old_value}'" if value.nil? value end
Walk the chain of has-many-throughs, collecting all tables we will need to join
# File lib/scoped_search/query_builder.rb, line 275 def nested_has_many(many_class, relation) acc = [relation] while (reflection = definition.reflection_by_name(many_class, relation)) break if reflection.nil? || reflection.options[:through].nil? relation = reflection.options[:through] acc.unshift(relation) end acc.map { |relation| definition.reflection_by_name(many_class, relation) } end
# File lib/scoped_search/query_builder.rb, line 98 def order_by(order, &block) field, direction_name = find_field_for_order_by(order, &block) return nil if field.nil? sql = field.to_sql(&block) direction = (!direction_name.nil? && direction_name.downcase.eql?('desc')) ? " DESC" : " ASC" return sql + direction end
# File lib/scoped_search/query_builder.rb, line 247 def preprocess_parameters(field, operator, value, &block) values = if [:in, :notin].include?(operator) value.split(',').map { |v| map_value(field, field.set? ? translate_value(field, v) : v.strip) } elsif [:like, :unlike].include?(operator) [(value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%')] else [map_value(field, field.offset ? value.to_i : value)] end values.each { |value| yield(:parameter, value) } 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, line 191 def set_test(field, operator,value, &block) set_value = translate_value(field, value) raise ScopedSearch::QueryNotSupported, "Operator '#{operator}' not supported for '#{field.field}'" unless [:eq,:ne].include?(operator) negate = '' if [true,false].include?(set_value) negate = 'NOT ' if operator == :ne if field.numerical? operator = (set_value == true) ? :gt : :eq set_value = 0 else operator = (set_value == true) ? :ne : :eq set_value = false end end ["#{negate}(#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?)", set_value] 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, line 115 def sql_operator(operator, field) 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? SQL_OPERATORS[operator] 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, line 216 def sql_test(field, operator, value, lhs, &block) # :yields: finder_option_type, value return field.to_ext_method_sql(lhs, sql_operator(operator, field), value, &block) if field.virtual? yield(:keyparameter, lhs.sub(/^.*\./,'')) if field.key_field condition, *values = if field.temporal? datetime_test(field, operator, value, &block) elsif field.set? set_test(field, operator, value, &block) else ["#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} #{value_placeholders(operator, value)}", value] end values.each { |value| preprocess_parameters(field, operator, value, &block) } if field.relation && definition.reflection_by_name(field.definition.klass, field.relation).macro == :has_many connection = field.definition.klass.connection reflection = definition.reflection_by_name(field.definition.klass, field.relation) primary_key_col = reflection.options[:primary_key] || field.definition.klass.primary_key primary_key = "#{connection.quote_table_name(field.definition.klass.table_name)}.#{connection.quote_column_name(primary_key_col)}" key, join_table = if reflection.options.has_key?(:through) [primary_key, has_many_through_join(field)] else [connection.quote_column_name(field.reflection_keys(reflection)[1]), connection.quote_table_name(field.klass.table_name)] end condition = "#{primary_key} IN (SELECT #{key} FROM #{join_table} WHERE #{condition} )" end condition end
Returns a NOT (…) SQL fragment that negates the current AST node's children
# File lib/scoped_search/query_builder.rb, line 121 def to_not_sql(rhs, definition, &block) "NOT COALESCE(#{rhs.to_sql(self, definition, &block)}, 0)" end
Validate the key name is in the set and translate the value to the set value.
# File lib/scoped_search/query_builder.rb, line 175 def translate_value(field, value) translated_value = field.complete_value[value.to_sym] raise ScopedSearch::QueryNotSupported, "'#{field.field}' should be one of '#{field.complete_value.keys.join(', ')}', but the query was '#{value}'" if translated_value.nil? translated_value end
# File lib/scoped_search/query_builder.rb, line 258 def value_placeholders(operator, value) return '?' unless [:in, :notin].include?(operator) '(' + value.split(',').map { '?' }.join(',') + ')' end
# File lib/scoped_search/query_builder.rb, line 310 def with_polymorphism?(reflection) as = reflection.options[:as] return unless as definition.reflection_by_name(reflection.klass, as).options[:polymorphic] end