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 [] 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       negate    = (operator == :ne) ? 'NOT ' : ''
153       field_sql = field.to_sql(operator, &block)
154       return ["#{negate}(#{field_sql} >= ? AND #{field_sql} < ?)", timestamp, timestamp + span]
155 
156     elsif operator == :gt
157       # Make sure timestamps on the given date are not included in the results
158       # by moving the date to the next day.
159       timestamp += span
160       operator = :gte
161 
162     elsif operator == :lte
163       # Make sure the timestamps of the given date are included by moving the
164       # date to the next date.
165       timestamp += span
166       operator = :lt
167     end
168   end
169 
170   # return the SQL test
171   ["#{field.to_sql(operator, &block)} #{sql_operator(operator, field)} ?", timestamp]
172 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
181 def map_value(field, value)
182   old_value = value
183   translator = field.value_translation
184   value = translator.call(value) if translator
185   raise ScopedSearch::QueryNotSupported, "Translation from any value to nil is not allowed, translated '#{old_value}'" if value.nil?
186   value
187 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
preprocess_parameters(field, operator, value) { |:parameter, value| ... } click to toggle source
    # File lib/scoped_search/query_builder.rb
245 def preprocess_parameters(field, operator, value, &block)
246   values = if [:in, :notin].include?(operator)
247              value.split(',').map { |v| map_value(field, field.set? ? translate_value(field, v) : v.strip) }
248            elsif [:like, :unlike].include?(operator)
249              [(value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%')]
250            else
251              [map_value(field, field.offset ? value.to_i : value)]
252            end
253   values.each { |value| yield(:parameter, value) }
254 end
set_test(field, operator,value, &block) 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
191 def set_test(field, operator,value, &block)
192   set_value = translate_value(field, value)
193   raise ScopedSearch::QueryNotSupported, "Operator '#{operator}' not supported for '#{field.field}'" unless [:eq,:ne].include?(operator)
194   negate = ''
195   if [true,false].include?(set_value)
196     negate = 'NOT ' if operator == :ne
197     if field.numerical?
198       operator =  (set_value == true) ?  :gt : :eq
199       set_value = 0
200     else
201       operator = (set_value == true) ? :ne : :eq
202       set_value = false
203     end
204   end
205   ["#{negate}(#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?)", set_value]
206 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
216 def sql_test(field, operator, value, lhs, &block) # :yields: finder_option_type, value
217   return field.to_ext_method_sql(lhs, sql_operator(operator, field), value, &block) if field.virtual?
218 
219   yield(:keyparameter, lhs.sub(/^.*\./,'')) if field.key_field
220 
221   condition, *values = if field.temporal?
222                          datetime_test(field, operator, value, &block)
223                        elsif field.set?
224                          set_test(field, operator, value, &block)
225                        else
226                          ["#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} #{value_placeholders(operator, value)}", value]
227                        end
228   values.each { |value| preprocess_parameters(field, operator, value, &block) }
229 
230   if field.relation && definition.reflection_by_name(field.definition.klass, field.relation).macro == :has_many
231     connection = field.definition.klass.connection
232     primary_key = "#{connection.quote_table_name(field.definition.klass.table_name)}.#{connection.quote_column_name(field.definition.klass.primary_key)}"
233     key, join_table = if definition.reflection_by_name(field.definition.klass, field.relation).options.has_key?(:through)
234                         [primary_key, has_many_through_join(field)]
235                       else
236                         [connection.quote_column_name(field.reflection_keys(definition.reflection_by_name(field.definition.klass, field.relation))[1]),
237                          connection.quote_table_name(field.klass.table_name)]
238                       end
239 
240     condition = "#{primary_key} IN (SELECT #{key} FROM #{join_table} WHERE #{condition} )"
241   end
242   condition
243 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
175 def translate_value(field, value)
176   translated_value = field.complete_value[value.to_sym]
177   raise ScopedSearch::QueryNotSupported, "'#{field.field}' should be one of '#{field.complete_value.keys.join(', ')}', but the query was '#{value}'" if translated_value.nil?
178   translated_value
179 end
value_placeholders(operator, value) click to toggle source
    # File lib/scoped_search/query_builder.rb
256 def value_placeholders(operator, value)
257   return '?' unless [:in, :notin].include?(operator)
258 
259   '(' + value.split(',').map { '?' }.join(',') + ')'
260 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