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
252 def find_has_many_through_association(field, through)
253   middle_table_association = nil
254   field.klass.reflect_on_all_associations(:has_many).each do |reflection|
255     class_name = reflection.options[:class_name].constantize.table_name if reflection.options[:class_name]
256     middle_table_association = reflection.name if class_name == through.to_s
257     middle_table_association = reflection.plural_name if reflection.plural_name == through.to_s
258   end
259   middle_table_association
260 end
has_many_through_join(field) click to toggle source
    # File lib/scoped_search/query_builder.rb
262     def has_many_through_join(field)
263       many_class = field.definition.klass
264       through = definition.reflection_by_name(many_class, field.relation).options[:through]
265       connection = many_class.connection
266 
267       # table names
268       endpoint_table_name = field.klass.table_name
269       many_table_name = many_class.table_name
270       middle_table_name = definition.reflection_by_name(many_class, through).klass.table_name
271 
272       # primary and foreign keys + optional condition for the many to middle join
273       pk1, fk1   = field.reflection_keys(definition.reflection_by_name(many_class, through))
274       condition1 = field.reflection_conditions(definition.reflection_by_name(field.klass, middle_table_name))
275 
276       # primary and foreign keys + optional condition for the endpoint to middle join
277       middle_table_association = find_has_many_through_association(field, through) || middle_table_name
278       pk2, fk2   = field.reflection_keys(definition.reflection_by_name(field.klass, middle_table_association))
279       condition2 = field.reflection_conditions(definition.reflection_by_name(many_class, field.relation))
280 
281       <<-SQL
282         #{connection.quote_table_name(many_table_name)}
283         INNER JOIN #{connection.quote_table_name(middle_table_name)}
284         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}
285         INNER JOIN #{connection.quote_table_name(endpoint_table_name)}
286         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}
287       SQL
288     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
186 def set_test(field, operator,value, &block)
187   set_value = translate_value(field, value)
188   raise ScopedSearch::QueryNotSupported, "Operator '#{operator}' not supported for '#{field.field}'" unless [:eq,:ne].include?(operator)
189   negate = ''
190   if [true,false].include?(set_value)
191     negate = 'NOT ' if operator == :ne
192     if field.numerical?
193       operator =  (set_value == true) ?  :gt : :eq
194       set_value = 0
195     else
196       operator = (set_value == true) ? :ne : :eq
197       set_value = false
198     end
199   end
200   yield(:parameter, set_value)
201   return "#{negate}(#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?)"
202 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 [: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
212 def sql_test(field, operator, value, lhs, &block) # :yields: finder_option_type, value
213   return field.to_ext_method_sql(lhs, sql_operator(operator, field), value, &block) if field.ext_method
214 
215   yield(:keyparameter, lhs.sub(/^.*\./,'')) if field.key_field
216 
217   if [:like, :unlike].include?(operator)
218     yield(:parameter, (value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%'))
219     return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?"
220 
221   elsif [:in, :notin].include?(operator)
222     value.split(',').collect { |v| yield(:parameter, field.set? ? translate_value(field, v) : v.strip) }
223     value = value.split(',').collect { "?" }.join(",")
224     return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} (#{value})"
225 
226   elsif field.temporal?
227     return datetime_test(field, operator, value, &block)
228 
229   elsif field.set?
230     return set_test(field, operator, value, &block)
231 
232   elsif field.relation && definition.reflection_by_name(field.definition.klass, field.relation).macro == :has_many
233     value = value.to_i if field.offset
234     yield(:parameter, value)
235     connection = field.definition.klass.connection
236     primary_key = "#{connection.quote_table_name(field.definition.klass.table_name)}.#{connection.quote_column_name(field.definition.klass.primary_key)}"
237     if definition.reflection_by_name(field.definition.klass, field.relation).options.has_key?(:through)
238       join = has_many_through_join(field)
239       return "#{primary_key} IN (SELECT #{primary_key} FROM #{join} WHERE #{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ? )"
240     else
241       foreign_key = connection.quote_column_name(field.reflection_keys(definition.reflection_by_name(field.definition.klass, field.relation))[1])
242       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)} ? )"
243     end
244 
245   else
246     value = value.to_i if field.offset
247     yield(:parameter, value)
248     return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?"
249   end
250 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