Class ScopedSearch::QueryBuilder
In: lib/scoped_search/query_builder.rb
Parent: Object

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.

Methods

Classes and Modules

Module ScopedSearch::QueryBuilder::AST
Module ScopedSearch::QueryBuilder::Field
Class ScopedSearch::QueryBuilder::Mysql2Adapter
Class ScopedSearch::QueryBuilder::MysqlAdapter
Class ScopedSearch::QueryBuilder::OracleEnhancedAdapter
Class ScopedSearch::QueryBuilder::PostgreSQLAdapter

Constants

SQL_OPERATORS = { :eq =>'=', :ne => '<>', :like => 'LIKE', :unlike => 'NOT LIKE', :gt => '>', :lt =>'<', :lte => '<=', :gte => '>=', :in => 'IN',:notin => 'NOT IN' }   A hash that maps the operators of the query language with the corresponding SQL operator.

Attributes

ast  [R] 
definition  [R] 

Public Class methods

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.

[Source]

    # File lib/scoped_search/query_builder.rb, line 17
17:     def self.build_query(definition, *args)
18:       query = args[0] ||=''
19:       options = args[1] || {}
20: 
21:       query_builder_class = self.class_for(definition)
22:       if query.kind_of?(ScopedSearch::QueryLanguage::AST::Node)
23:         return query_builder_class.new(definition, query, options[:profile]).build_find_params(options)
24:       elsif query.kind_of?(String)
25:         return query_builder_class.new(definition, ScopedSearch::QueryLanguage::Compiler.parse(query), options[:profile]).build_find_params(options)
26:       else
27:         raise "Unsupported query object: #{query.inspect}!"
28:       end
29:     end

Loads the QueryBuilder class for the connection of the given definition. If no specific adapter is found, the default QueryBuilder class is returned.

[Source]

    # File lib/scoped_search/query_builder.rb, line 33
33:     def self.class_for(definition)
34:       self.const_get(definition.klass.connection.class.name.split('::').last)
35:     rescue
36:       self
37:     end

Initializes the instance by setting the relevant parameters

[Source]

    # File lib/scoped_search/query_builder.rb, line 40
40:     def initialize(definition, ast, profile)
41:       @definition, @ast, @definition.profile = definition, ast, profile
42:     end

Public Instance methods

Actually builds the find parameters hash that should be used in the search_for named scope.

[Source]

    # File lib/scoped_search/query_builder.rb, line 46
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:       find_attributes[:group]      = options[:group]    unless options[:group].nil?
85: 
86:       # p find_attributes # Uncomment for debugging
87:       return find_attributes
88:     end

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.

[Source]

     # File lib/scoped_search/query_builder.rb, line 132
132:     def datetime_test(field, operator, value, &block) # :yields: finder_option_type, value
133: 
134:       # Parse the value as a date/time and ignore invalid timestamps
135:       timestamp = definition.parse_temporal(value)
136:       return nil unless timestamp
137: 
138:       timestamp = timestamp.to_date if field.date?
139:       # Check for the case that a date-only value is given as search keyword,
140:       # but the field is of datetime type. Change the comparison to return
141:       # more logical results.
142:       if field.datetime?
143:         span = 1.minute if(value =~ /\A\s*\d+\s+\bminutes?\b\s+\bago\b\s*\z/i)
144:         span ||= (timestamp.day_fraction == 0) ? 1.day :  1.hour
145:         if [:eq, :ne].include?(operator)
146:           # Instead of looking for an exact (non-)match, look for dates that
147:           # fall inside/outside the range of timestamps of that day.
148:           yield(:parameter, timestamp)
149:           yield(:parameter, timestamp + span)
150:           negate    = (operator == :ne) ? 'NOT ' : ''
151:           field_sql = field.to_sql(operator, &block)
152:           return "#{negate}(#{field_sql} >= ? AND #{field_sql} < ?)"
153: 
154:         elsif operator == :gt
155:           # Make sure timestamps on the given date are not included in the results
156:           # by moving the date to the next day.
157:           timestamp += span
158:           operator = :gte
159: 
160:         elsif operator == :lte
161:           # Make sure the timestamps of the given date are included by moving the
162:           # date to the next date.
163:           timestamp += span
164:           operator = :lt
165:         end
166:       end
167: 
168:       # Yield the timestamp and return the SQL test
169:       yield(:parameter, timestamp)
170:       "#{field.to_sql(operator, &block)} #{sql_operator(operator, field)} ?"
171:     end

[Source]

     # File lib/scoped_search/query_builder.rb, line 90
 90:     def order_by(order, &block)
 91:       order ||= definition.default_order
 92:       if order
 93:         field = definition.field_by_name(order.to_s.split(' ')[0])
 94:         raise ScopedSearch::QueryNotSupported, "the field '#{order.to_s.split(' ')[0]}' in the order statement is not valid field for search" unless field
 95:         sql = field.to_sql(&block)
 96:         direction = (order.to_s.downcase.include?('desc')) ? " DESC" : " ASC"
 97:         order = sql + direction
 98:       end
 99:       return order
100:     end

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.

[Source]

     # File lib/scoped_search/query_builder.rb, line 182
182:     def set_test(field, operator,value, &block)
183:       set_value = translate_value(field, value)
184:       raise ScopedSearch::QueryNotSupported, "Operator '#{operator}' not supported for '#{field.field}'" unless [:eq,:ne].include?(operator)
185:       negate = ''
186:       if [true,false].include?(set_value)
187:         negate = 'NOT ' if operator == :ne
188:         if field.numerical?
189:           operator =  (set_value == true) ?  :gt : :eq
190:           set_value = 0
191:         else
192:           operator = (set_value == true) ? :ne : :eq
193:           set_value = false
194:         end
195:       end
196:       yield(:parameter, set_value)
197:       return "#{negate}(#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?)"
198:     end

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.

[Source]

     # File lib/scoped_search/query_builder.rb, line 111
111:     def sql_operator(operator, field)
112:       raise ScopedSearch::QueryNotSupported, "the operator '#{operator}' is not supported for field type '#{field.type}'" if [:like, :unlike].include?(operator) and !field.textual?
113:       SQL_OPERATORS[operator]
114:     end

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.

[Source]

     # File lib/scoped_search/query_builder.rb, line 208
208:     def sql_test(field, operator, value, lhs, &block) # :yields: finder_option_type, value
209:       return field.to_ext_method_sql(lhs, sql_operator(operator, field), value, &block) if field.ext_method
210: 
211:       yield(:keyparameter, lhs.sub(/^.*\./,'')) if field.key_field
212: 
213:       if [:like, :unlike].include?(operator)
214:         yield(:parameter, (value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%'))
215:         return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?"
216:       elsif [:in, :notin].include?(operator)
217:         value.split(',').collect { |v| yield(:parameter, field.set? ? translate_value(field, v) : v.strip) }
218:         value = value.split(',').collect { "?" }.join(",")
219:         return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} (#{value})"
220:       elsif field.temporal?
221:         return datetime_test(field, operator, value, &block)
222:       elsif field.set?
223:         return set_test(field, operator, value, &block)
224:       else
225:         value = value.to_i if field.offset
226:         yield(:parameter, value)
227:         return "#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?"
228:       end
229:     end

Returns a NOT (…) SQL fragment that negates the current AST node‘s children

[Source]

     # File lib/scoped_search/query_builder.rb, line 117
117:     def to_not_sql(rhs, definition, &block)
118:       "NOT COALESCE(#{rhs.to_sql(self, definition, &block)}, 0)"
119:     end

Validate the key name is in the set and translate the value to the set value.

[Source]

     # File lib/scoped_search/query_builder.rb, line 174
174:     def translate_value(field, value)
175:       translated_value = field.complete_value[value.to_sym]
176:       raise ScopedSearch::QueryNotSupported, "'#{field.field}' should be one of '#{field.complete_value.keys.join(', ')}', but the query was '#{value}'" if translated_value.nil?
177:       translated_value
178:     end

[Validate]