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

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

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
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
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
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
find_field_for_order_by(order, &block) click to toggle source
   # 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
find_has_many_through_association(field, through) click to toggle source
    # File lib/scoped_search/query_builder.rb
262 def find_has_many_through_association(field, through)
263   middle_table_association = nil
264   field.klass.reflect_on_all_associations(:has_many).each do |reflection|
265     class_name = reflection.options[:class_name].constantize.table_name if reflection.options[:class_name]
266     middle_table_association = reflection.name if class_name == through.to_s
267     middle_table_association = reflection.plural_name if reflection.plural_name == through.to_s
268   end
269   middle_table_association
270 end
has_many_through_join(field) click to toggle source
    # File lib/scoped_search/query_builder.rb
272     def has_many_through_join(field)
273       many_class = field.definition.klass
274       through = definition.reflection_by_name(many_class, field.relation).options[:through]
275       through_class = definition.reflection_by_name(many_class, through).klass
276 
277       connection = many_class.connection
278 
279       # table names
280       endpoint_table_name = field.klass.table_name
281       many_table_name = many_class.table_name
282       middle_table_name = through_class.table_name
283 
284       # primary and foreign keys + optional conditions for the joins
285       pk1, fk1   = field.reflection_keys(definition.reflection_by_name(many_class, through))
286       condition_many_to_middle = if with_polymorphism?(many_class, field.klass, through, through_class)
287                                    field.reflection_conditions(definition.reflection_by_name(field.klass, many_table_name))
288                                  else
289                                    ''
290                                  end
291       condition_middle_to_end = field.reflection_conditions(definition.reflection_by_name(field.klass, middle_table_name))
292 
293       # primary and foreign keys + optional condition for the endpoint to middle join
294       middle_table_association = find_has_many_through_association(field, through) || middle_table_name
295       pk2, fk2   = field.reflection_keys(definition.reflection_by_name(field.klass, middle_table_association))
296       condition2 = field.reflection_conditions(definition.reflection_by_name(many_class, field.relation))
297 
298       <<-SQL
299         #{connection.quote_table_name(many_table_name)}
300         INNER JOIN #{connection.quote_table_name(middle_table_name)}
301         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)} #{condition_many_to_middle} #{condition_middle_to_end}
302         INNER JOIN #{connection.quote_table_name(endpoint_table_name)}
303         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}
304       SQL
305     end
map_value(field, value) click to toggle source
    # File lib/scoped_search/query_builder.rb
184 def map_value(field, value)
185   old_value = value
186   translator = field.value_translation
187   value = translator.call(value) if translator
188   raise ScopedSearch::QueryNotSupported, "Translation from any value to nil is not allowed, translated '#{old_value}'" if value.nil?
189   value
190 end
order_by(order, &block) click to toggle source
    # 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
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
194 def set_test(field, operator,value, &block)
195   set_value = translate_value(field, value)
196   raise ScopedSearch::QueryNotSupported, "Operator '#{operator}' not supported for '#{field.field}'" unless [:eq,:ne].include?(operator)
197   negate = ''
198   if [true,false].include?(set_value)
199     negate = 'NOT ' if operator == :ne
200     if field.numerical?
201       operator =  (set_value == true) ?  :gt : :eq
202       set_value = 0
203     else
204       operator = (set_value == true) ? :ne : :eq
205       set_value = false
206     end
207   end
208   yield(:parameter, set_value)
209   return "#{negate}(#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?)"
210 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
115 def sql_operator(operator, field)
116   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?
117   SQL_OPERATORS[operator]
118 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
220 def sql_test(field, operator, value, lhs, &block) # :yields: finder_option_type, value
221   return field.to_ext_method_sql(lhs, sql_operator(operator, field), value, &block) if field.virtual?
222 
223   yield(:keyparameter, lhs.sub(/^.*\./,'')) if field.key_field
224 
225   if [:like, :unlike].include?(operator)
226     yield(:parameter, (value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%'))
227     return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?"
228 
229   elsif [:in, :notin].include?(operator)
230     value.split(',').collect { |v| yield(:parameter, map_value(field, field.set? ? translate_value(field, v) : v.strip)) }
231     value = value.split(',').collect { "?" }.join(",")
232     return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} (#{value})"
233 
234   elsif field.temporal?
235     return datetime_test(field, operator, value, &block)
236 
237   elsif field.set?
238     return set_test(field, operator, value, &block)
239 
240   elsif field.relation && definition.reflection_by_name(field.definition.klass, field.relation).macro == :has_many
241     value = value.to_i if field.offset
242     value = map_value(field, value)
243     yield(:parameter, value)
244     connection = field.definition.klass.connection
245     primary_key = "#{connection.quote_table_name(field.definition.klass.table_name)}.#{connection.quote_column_name(field.definition.klass.primary_key)}"
246     if definition.reflection_by_name(field.definition.klass, field.relation).options.has_key?(:through)
247       join = has_many_through_join(field)
248       return "#{primary_key} IN (SELECT #{primary_key} FROM #{join} WHERE #{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ? )"
249     else
250       foreign_key = connection.quote_column_name(field.reflection_keys(definition.reflection_by_name(field.definition.klass, field.relation))[1])
251       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)} ? )"
252     end
253 
254   else
255     value = value.to_i if field.offset
256     value = map_value(field, value)
257     yield(:parameter, value)
258     return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?"
259   end
260 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
121 def to_not_sql(rhs, definition, &block)
122   "NOT COALESCE(#{rhs.to_sql(self, definition, &block)}, 0)"
123 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
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
with_polymorphism?(many_class, endpoint_class, through, through_class) click to toggle source
    # File lib/scoped_search/query_builder.rb
307 def with_polymorphism?(many_class, endpoint_class, through, through_class)
308   reflections = [definition.reflection_by_name(endpoint_class, through), definition.reflection_by_name(many_class, through)].compact
309   as = reflections.map(&:options).compact.map { |opt| opt[:as] }.compact
310   return false if as.empty?
311   definition.reflection_by_name(through_class, as.first).options[:polymorphic]
312 end