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 = definition
42   @ast = ast
43   @definition.profile = profile
44 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
48 def build_find_params(options)
49   keyconditions = []
50   keyparameters = []
51   parameters = []
52   includes   = []
53   joins   = []
54 
55   # Build SQL WHERE clause using the AST
56   sql = @ast.to_sql(self, definition) do |notification, value|
57 
58     # Handle the notifications encountered during the SQL generation:
59     # Store the parameters, includes, etc so that they can be added to
60     # the find-hash later on.
61     case notification
62       when :keycondition then keyconditions << value
63       when :keyparameter then keyparameters << value
64       when :parameter    then parameters    << value
65       when :include      then includes      << value
66       when :joins        then joins         << value
67       else raise ScopedSearch::QueryNotSupported, "Cannot handle #{notification.inspect}: #{value.inspect}"
68     end
69   end
70     # Build SQL ORDER BY clause
71   order = order_by(options[:order]) do |notification, value|
72     case notification
73       when :parameter then parameters << value
74       when :include   then includes   << value
75       when :joins     then joins      << value
76       else raise ScopedSearch::QueryNotSupported, "Cannot handle #{notification.inspect}: #{value.inspect}"
77     end
78   end
79   sql = (keyconditions + (sql.blank? ? [] : [sql]) ).map {|c| "(#{c})"}.join(" AND ")
80   # Build hash for ActiveRecord::Base#find for the named scope
81   find_attributes = {}
82   find_attributes[:conditions] = [sql] + keyparameters + parameters unless sql.blank?
83   find_attributes[:include]    = includes.uniq                      unless includes.empty?
84   find_attributes[:joins]      = joins.uniq                         unless joins.empty?
85   find_attributes[:order]      = order                              unless order.nil?
86 
87   # p find_attributes # Uncomment for debugging
88   return find_attributes
89 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
138 def datetime_test(field, operator, value, &block) # :yields: finder_option_type, value
139 
140   # Parse the value as a date/time and ignore invalid timestamps
141   timestamp = definition.parse_temporal(value)
142   return [] unless timestamp
143 
144   timestamp = timestamp.to_date if field.date?
145   # Check for the case that a date-only value is given as search keyword,
146   # but the field is of datetime type. Change the comparison to return
147   # more logical results.
148   if field.datetime?
149     span = 1.minute if(value =~ /\A\s*\d+\s+\bminutes?\b\s+\bago\b\s*\z/i)
150     span ||= (timestamp.day_fraction == 0) ? 1.day : 1.hour
151     if [:eq, :ne].include?(operator)
152       # Instead of looking for an exact (non-)match, look for dates that
153       # fall inside/outside the range of timestamps of that day.
154       negate    = (operator == :ne) ? 'NOT ' : ''
155       field_sql = field.to_sql(operator, &block)
156       return ["#{negate}(#{field_sql} >= ? AND #{field_sql} < ?)", timestamp, timestamp + span]
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   # return the SQL test
173   ["#{field.to_sql(operator, &block)} #{sql_operator(operator, field)} ?", timestamp]
174 end
find_field_for_order_by(order, &block) click to toggle source
   # File lib/scoped_search/query_builder.rb
91 def find_field_for_order_by(order, &block)
92   order ||= definition.default_order
93   return [nil, nil] if order.blank?
94   field_name, direction_name = order.to_s.split(/\s+/, 2)
95   field = definition.field_by_name(field_name)
96   raise ScopedSearch::QueryNotSupported, "the field '#{field_name}' in the order statement is not valid field for search" unless field
97   return field, direction_name
98 end
find_has_many_through_association(field, through) click to toggle source
    # File lib/scoped_search/query_builder.rb
266 def find_has_many_through_association(field, through)
267   middle_table_association = nil
268   field.klass.reflect_on_all_associations(:has_many).each do |reflection|
269     class_name = reflection.options[:class_name].constantize.table_name if reflection.options[:class_name]
270     middle_table_association = reflection.name if class_name == through.to_s
271     middle_table_association = reflection.plural_name if reflection.plural_name == through.to_s
272   end
273   middle_table_association
274 end
has_many_through_join(field) click to toggle source
    # File lib/scoped_search/query_builder.rb
287     def has_many_through_join(field)
288       many_class = field.definition.klass
289       connection = many_class.connection
290       sql = connection.quote_table_name(many_class.table_name)
291       join_reflections = nested_has_many(many_class, field.relation)
292       table_names = [[many_class.table_name, many_class.sti_name.tableize]] + join_reflections.map(&:table_name)
293 
294       join_reflections.zip(table_names.zip(join_reflections.drop(1))).reduce(sql) do |acc, (reflection, (previous_table, next_reflection))|
295         fk1, pk1 = if reflection.respond_to?(:join_keys)
296                      klass = reflection.method(:join_keys).arity == 1 ? [reflection.klass] : [] # ActiveRecord <5.2 workaround
297                      reflection.join_keys(*klass).values # We are joining the tables "in reverse", so the PK and FK are swapped
298                    else
299                      [reflection.join_primary_key, reflection.join_foreign_key] #ActiveRecord 6.1
300                    end
301 
302         previous_table, sti_name = previous_table
303         # primary and foreign keys + optional conditions for the joins
304         join_condition = if with_polymorphism?(reflection)
305                            field.reflection_conditions(definition.reflection_by_name(next_reflection.klass, sti_name || previous_table))
306                          else
307                            ''
308                          end
309 
310         acc + <<-SQL
311           INNER JOIN #{connection.quote_table_name(reflection.table_name)}
312           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}
313         SQL
314       end
315     end
map_value(field, value) click to toggle source
    # File lib/scoped_search/query_builder.rb
183 def map_value(field, value)
184   old_value = value
185   translator = field.value_translation
186   value = translator.call(value) if translator
187   raise ScopedSearch::QueryNotSupported, "Translation from any value to nil is not allowed, translated '#{old_value}'" if value.nil?
188   value
189 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
277 def nested_has_many(many_class, relation)
278   acc = [relation]
279   while (reflection = definition.reflection_by_name(many_class, relation))
280     break if reflection.nil? || reflection.options[:through].nil?
281     relation = reflection.options[:through]
282     acc.unshift(relation)
283   end
284   acc.map { |relation| definition.reflection_by_name(many_class, relation) }
285 end
order_by(order, &block) click to toggle source
    # File lib/scoped_search/query_builder.rb
100 def order_by(order, &block)
101   field, direction_name = find_field_for_order_by(order, &block)
102   return nil if field.nil?
103   sql = field.to_sql(&block)
104   direction = (!direction_name.nil? && direction_name.downcase.eql?('desc')) ? " DESC" : " ASC"
105   return sql + direction
106 end
preprocess_parameters(field, operator, value) { |:parameter, value| ... } click to toggle source
    # File lib/scoped_search/query_builder.rb
249 def preprocess_parameters(field, operator, value, &block)
250   values = if [:in, :notin].include?(operator)
251              value.split(',').map { |v| map_value(field, field.set? ? translate_value(field, v) : v.strip) }
252            elsif [:like, :unlike].include?(operator)
253              [(value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%')]
254            else
255              [map_value(field, field.offset ? value.to_i : value)]
256            end
257   values.each { |value| yield(:parameter, value) }
258 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
193 def set_test(field, operator,value, &block)
194   set_value = translate_value(field, value)
195   raise ScopedSearch::QueryNotSupported, "Operator '#{operator}' not supported for '#{field.field}'" unless [:eq,:ne].include?(operator)
196   negate = ''
197   if [true,false].include?(set_value)
198     negate = 'NOT ' if operator == :ne
199     if field.numerical?
200       operator =  (set_value == true) ?  :gt : :eq
201       set_value = 0
202     else
203       operator = (set_value == true) ? :ne : :eq
204       set_value = false
205     end
206   end
207   ["#{negate}(#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?)", set_value]
208 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
117 def sql_operator(operator, field)
118   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?
119   SQL_OPERATORS[operator]
120 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
218 def sql_test(field, operator, value, lhs, &block) # :yields: finder_option_type, value
219   return field.to_ext_method_sql(lhs, sql_operator(operator, field), value, &block) if field.virtual?
220 
221   yield(:keyparameter, lhs.sub(/^.*\./,'')) if field.key_field
222 
223   condition, *values = if field.temporal?
224                          datetime_test(field, operator, value, &block)
225                        elsif field.set?
226                          set_test(field, operator, value, &block)
227                        else
228                          ["#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} #{value_placeholders(operator, value)}", value]
229                        end
230   values.each { |value| preprocess_parameters(field, operator, value, &block) }
231 
232   if field.relation && definition.reflection_by_name(field.definition.klass, field.relation).macro == :has_many
233     connection = field.definition.klass.connection
234     reflection = definition.reflection_by_name(field.definition.klass, field.relation)
235     primary_key_col = reflection.options[:primary_key] || field.definition.klass.primary_key
236     primary_key = "#{connection.quote_table_name(field.definition.klass.table_name)}.#{connection.quote_column_name(primary_key_col)}"
237     key, join_table = if reflection.options.has_key?(:through)
238                         [primary_key, has_many_through_join(field)]
239                       else
240                         [connection.quote_column_name(field.reflection_keys(reflection)[1]),
241                          connection.quote_table_name(field.klass.table_name)]
242                       end
243 
244     condition = "#{primary_key} IN (SELECT #{key} FROM #{join_table} WHERE #{condition} )"
245   end
246   condition
247 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
123 def to_not_sql(rhs, definition, &block)
124   "NOT COALESCE(#{rhs.to_sql(self, definition, &block)}, 0)"
125 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
177 def translate_value(field, value)
178   translated_value = field.complete_value[value.to_sym]
179   raise ScopedSearch::QueryNotSupported, "'#{field.field}' should be one of '#{field.complete_value.keys.join(', ')}', but the query was '#{value}'" if translated_value.nil?
180   translated_value
181 end
value_placeholders(operator, value) click to toggle source
    # File lib/scoped_search/query_builder.rb
260 def value_placeholders(operator, value)
261   return '?' unless [:in, :notin].include?(operator)
262 
263   '(' + value.split(',').map { '?' }.join(',') + ')'
264 end
with_polymorphism?(reflection) click to toggle source
    # File lib/scoped_search/query_builder.rb
317 def with_polymorphism?(reflection)
318   as = reflection.options[:as]
319   return unless as
320   definition.reflection_by_name(reflection.klass, as).options[:polymorphic]
321 end