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

ast[R]
definition[R]

Public Class Methods

build_query(definition, query, options = {}) click to toggle source

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
class_for(definition) click to toggle source

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
new(definition, ast, profile) click to toggle source

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

build_find_params(options) click to toggle source

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
datetime_test(field, operator, value) { |finder_option_type, value| ... } click to toggle source

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 nil 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.
      yield(:parameter, timestamp)
      yield(:parameter, timestamp + span)
      negate    = (operator == :ne) ? 'NOT ' : ''
      field_sql = field.to_sql(operator, &block)
      return "#{negate}(#{field_sql} >= ? AND #{field_sql} < ?)"

    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

  # Yield the timestamp and return the SQL test
  yield(:parameter, timestamp)
  "#{field.to_sql(operator, &block)} #{sql_operator(operator, field)} ?"
end
find_field_for_order_by(order, &block) click to toggle source
# 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
find_has_many_through_association(field, through) click to toggle source
# File lib/scoped_search/query_builder.rb, line 252
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
has_many_through_join(field) click to toggle source
# File lib/scoped_search/query_builder.rb, line 262
def has_many_through_join(field)
  many_class = field.definition.klass
  through = definition.reflection_by_name(many_class, field.relation).options[:through]
  connection = many_class.connection

  # table names
  endpoint_table_name = field.klass.table_name
  many_table_name = many_class.table_name
  middle_table_name = definition.reflection_by_name(many_class, through).klass.table_name

  # primary and foreign keys + optional condition for the many to middle join
  pk1, fk1   = field.reflection_keys(definition.reflection_by_name(many_class, through))
  condition1 = field.reflection_conditions(definition.reflection_by_name(field.klass, middle_table_name))

  # primary and foreign keys + optional condition for the endpoint to middle join
  middle_table_association = find_has_many_through_association(field, through) || middle_table_name
  pk2, fk2   = field.reflection_keys(definition.reflection_by_name(field.klass, middle_table_association))
  condition2 = field.reflection_conditions(definition.reflection_by_name(many_class, field.relation))

  <<-SQL
    #{connection.quote_table_name(many_table_name)}
    INNER JOIN #{connection.quote_table_name(middle_table_name)}
    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}
    INNER JOIN #{connection.quote_table_name(endpoint_table_name)}
    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}
  SQL
end
order_by(order, &block) click to toggle source
# 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
set_test(field, operator,value) { |:parameter, set_value| ... } click to toggle source

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 186
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
  yield(:parameter, set_value)
  return "#{negate}(#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?)"
end
sql_operator(operator, field) click to toggle source

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 [:like, :unlike].include?(operator) and !field.textual?
  SQL_OPERATORS[operator]
end
sql_test(field, operator, value, lhs) { |finder_option_type, value| ... } click to toggle source

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 212
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.ext_method

  yield(:keyparameter, lhs.sub(/^.*\./,'')) if field.key_field

  if [:like, :unlike].include?(operator)
    yield(:parameter, (value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%'))
    return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?"

  elsif [:in, :notin].include?(operator)
    value.split(',').collect { |v| yield(:parameter, field.set? ? translate_value(field, v) : v.strip) }
    value = value.split(',').collect { "?" }.join(",")
    return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} (#{value})"

  elsif field.temporal?
    return datetime_test(field, operator, value, &block)

  elsif field.set?
    return set_test(field, operator, value, &block)

  elsif field.relation && definition.reflection_by_name(field.definition.klass, field.relation).macro == :has_many
    value = value.to_i if field.offset
    yield(:parameter, value)
    connection = field.definition.klass.connection
    primary_key = "#{connection.quote_table_name(field.definition.klass.table_name)}.#{connection.quote_column_name(field.definition.klass.primary_key)}"
    if definition.reflection_by_name(field.definition.klass, field.relation).options.has_key?(:through)
      join = has_many_through_join(field)
      return "#{primary_key} IN (SELECT #{primary_key} FROM #{join} WHERE #{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ? )"
    else
      foreign_key = connection.quote_column_name(field.reflection_keys(definition.reflection_by_name(field.definition.klass, field.relation))[1])
      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)} ? )"
    end

  else
    value = value.to_i if field.offset
    yield(:parameter, value)
    return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?"
  end
end
to_not_sql(rhs, definition, &block) click to toggle source

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
translate_value(field, value) click to toggle source

Validate the key name is in the set and translate the value to the set value.

# File lib/scoped_search/query_builder.rb, line 178
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