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
264 def find_has_many_through_association(field, through)
265   middle_table_association = nil
266   field.klass.reflect_on_all_associations(:has_many).each do |reflection|
267     class_name = reflection.options[:class_name].constantize.table_name if reflection.options[:class_name]
268     middle_table_association = reflection.name if class_name == through.to_s
269     middle_table_association = reflection.plural_name if reflection.plural_name == through.to_s
270   end
271   middle_table_association
272 end
has_many_through_join(field) click to toggle source
    # File lib/scoped_search/query_builder.rb
285     def has_many_through_join(field)
286       many_class = field.definition.klass
287       connection = many_class.connection
288       sql = connection.quote_table_name(many_class.table_name)
289       join_reflections = nested_has_many(many_class, field.relation)
290       table_names = [many_class.table_name] + join_reflections.map(&:table_name)
291 
292       join_reflections.zip(table_names.zip(join_reflections.drop(1))).reduce(sql) do |acc, (reflection, (previous_table, next_reflection))|
293         klass = reflection.method(:join_keys).arity == 1 ? [reflection.klass] : [] # ActiveRecord <5.2 workaround
294         fk1, pk1 = reflection.join_keys(*klass).values # We are joining the tables "in reverse", so the PK and FK are swapped
295 
296         # primary and foreign keys + optional conditions for the joins
297         join_condition = if with_polymorphism?(reflection)
298                            field.reflection_conditions(definition.reflection_by_name(next_reflection.klass, previous_table))
299                          else
300                            ''
301                          end
302 
303         acc + <<-SQL
304           INNER JOIN #{connection.quote_table_name(reflection.table_name)}
305           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}
306         SQL
307       end
308     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
nested_has_many(many_class, relation) click to toggle source

Walk the chain of has-many-throughs, collecting all tables we will need to join

    # File lib/scoped_search/query_builder.rb
275 def nested_has_many(many_class, relation)
276   acc = [relation]
277   while (reflection = definition.reflection_by_name(many_class, relation))
278     break if reflection.nil? || reflection.options[:through].nil?
279     relation = reflection.options[:through]
280     acc.unshift(relation)
281   end
282   acc.map { |relation| definition.reflection_by_name(many_class, relation) }
283 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
247 def preprocess_parameters(field, operator, value, &block)
248   values = if [:in, :notin].include?(operator)
249              value.split(',').map { |v| map_value(field, field.set? ? translate_value(field, v) : v.strip) }
250            elsif [:like, :unlike].include?(operator)
251              [(value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%')]
252            else
253              [map_value(field, field.offset ? value.to_i : value)]
254            end
255   values.each { |value| yield(:parameter, value) }
256 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     reflection = definition.reflection_by_name(field.definition.klass, field.relation)
233     primary_key_col = reflection.options[:primary_key] || field.definition.klass.primary_key
234     primary_key = "#{connection.quote_table_name(field.definition.klass.table_name)}.#{connection.quote_column_name(primary_key_col)}"
235     key, join_table = if reflection.options.has_key?(:through)
236                         [primary_key, has_many_through_join(field)]
237                       else
238                         [connection.quote_column_name(field.reflection_keys(reflection)[1]),
239                          connection.quote_table_name(field.klass.table_name)]
240                       end
241 
242     condition = "#{primary_key} IN (SELECT #{key} FROM #{join_table} WHERE #{condition} )"
243   end
244   condition
245 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
258 def value_placeholders(operator, value)
259   return '?' unless [:in, :notin].include?(operator)
260 
261   '(' + value.split(',').map { '?' }.join(',') + ')'
262 end
with_polymorphism?(reflection) click to toggle source
    # File lib/scoped_search/query_builder.rb
310 def with_polymorphism?(reflection)
311   as = reflection.options[:as]
312   return unless as
313   definition.reflection_by_name(reflection.klass, as).options[:polymorphic]
314 end