Class: ScopedSearch::QueryBuilder

Inherits:
Object
  • Object
show all
Defined in:
lib/scoped_search/query_builder.rb

Overview

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.

Direct Known Subclasses

PostgreSQLAdapter

Defined Under Namespace

Modules: AST, Field Classes: PostgreSQLAdapter

Constant Summary collapse

SQL_OPERATORS =

A hash that maps the operators of the query language with the corresponding SQL operator.

{ :eq => '=',  :ne => '<>', :like => 'LIKE', :unlike => 'NOT LIKE',
:gt => '>',  :lt =>'<',   :lte => '<=',    :gte => '>=',
:in => 'IN', :notin => 'NOT IN' }

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(definition, ast, profile) ⇒ QueryBuilder

Initializes the instance by setting the relevant parameters



40
41
42
43
44
 
# File 'lib/scoped_search/query_builder.rb', line 40

def initialize(definition, ast, profile)
  @definition = definition
  @ast = ast
  @definition.profile = profile
end

Instance Attribute Details

#ast ⇒ Object (readonly)

Returns the value of attribute ast.



8
9
10
 
# File 'lib/scoped_search/query_builder.rb', line 8

def ast
  @ast
end

#definition ⇒ Object (readonly)

Returns the value of attribute definition.



8
9
10
 
# File 'lib/scoped_search/query_builder.rb', line 8

def definition
  @definition
end

Class Method Details

.build_query(definition, query, options = {}) ⇒ Object

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.



17
18
19
20
21
22
23
24
25
26
 
# File 'lib/scoped_search/query_builder.rb', line 17

def self.build_query(definition, query, options = {})
  query_builder_class = self.class_for(definition)
  if query.kind_of?(ScopedSearch::QueryLanguage::AST::Node)
    return query_builder_class.new(definition, query, options[:profile]).build_find_params(options)
  elsif query.kind_of?(String)
    return query_builder_class.new(definition, ScopedSearch::QueryLanguage::Compiler.parse(query), options[:profile]).build_find_params(options)
  else
    raise ArgumentError, "Unsupported query object: #{query.inspect}!"
  end
end

.class_for(definition) ⇒ Object

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



30
31
32
33
34
35
36
37
 
# File 'lib/scoped_search/query_builder.rb', line 30

def self.class_for(definition)
  case definition.klass.connection.class.name.split('::').last
  when /postgresql/i
    PostgreSQLAdapter
  else
    self
  end
end

Instance Method Details

#build_find_params(options) ⇒ Object

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



48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
 
# File 'lib/scoped_search/query_builder.rb', line 48

def build_find_params(options)
  keyconditions = []
  keyparameters = []
  parameters = []
  includes   = []
  joins   = []

  # Build SQL WHERE clause using the AST
  sql = @ast.to_sql(self, definition) do |notification, value|

    # Handle the notifications encountered during the SQL generation:
    # Store the parameters, includes, etc so that they can be added to
    # the find-hash later on.
    case notification
      when :keycondition then keyconditions << value
      when :keyparameter then keyparameters << value
      when :parameter    then parameters    << value
      when :include      then includes      << value
      when :joins        then joins         << value
      else raise ScopedSearch::QueryNotSupported, "Cannot handle #{notification.inspect}: #{value.inspect}"
    end
  end
    # Build SQL ORDER BY clause
  order = order_by(options[:order]) do |notification, value|
    case notification
      when :parameter then parameters << value
      when :include   then includes   << value
      when :joins     then joins      << value
      else raise ScopedSearch::QueryNotSupported, "Cannot handle #{notification.inspect}: #{value.inspect}"
    end
  end
  sql = (keyconditions + (sql.blank? ? [] : [sql]) ).map {|c| "(#{c})"}.join(" AND ")
  # Build hash for ActiveRecord::Base#find for the named scope
  find_attributes = {}
  find_attributes[:conditions] = [sql] + keyparameters + parameters unless sql.blank?
  find_attributes[:include]    = includes.uniq                      unless includes.empty?
  find_attributes[:joins]      = joins.uniq                         unless joins.empty?
  find_attributes[:order]      = order                              unless order.nil?

  # p find_attributes # Uncomment for debugging
  return find_attributes
end

#datetime_test(field, operator, value, &block) ⇒ Object

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.



138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
 
# File 'lib/scoped_search/query_builder.rb', line 138

def datetime_test(field, operator, value, &block) # :yields: finder_option_type, value

  # Parse the value as a date/time and ignore invalid timestamps
  timestamp = definition.parse_temporal(value)
  return [] unless timestamp

  timestamp = timestamp.to_date if field.date?
  # Check for the case that a date-only value is given as search keyword,
  # but the field is of datetime type. Change the comparison to return
  # more logical results.
  if field.datetime?
    if value =~ time_unit_regex("minutes?|hours?")
      span = 1.minute
    elsif value =~ time_unit_regex("days?|weeks?|months?|years?") || value =~ /\b(today|tomorrow|yesterday)\b/i
      span = 1.day
    else
      tokens = DateTime._parse(value)
      # find the smallest unit of time given in input and determine span for further adjustment of the search query
      span = {
        sec: 1.second,
        min: 1.minute,
        hour: 1.hour,
        mday: 1.day,
        mon: 1.month
      }.find { |key, _| tokens[key] }&.last || 1.year
    end

    if [:eq, :ne].include?(operator)
      # Instead of looking for an exact (non-)match, look for dates that
      # fall inside/outside the range of timestamps of that day.
      negate    = (operator == :ne) ? 'NOT ' : ''
      field_sql = field.to_sql(operator, &block)
      return ["#{negate}(#{field_sql} >= ? AND #{field_sql} < ?)", timestamp, timestamp + span]

    elsif span >= 1.day && operator == :gt
      # Make sure timestamps on the given date are not included in the results
      # by moving the date to the next day.
      timestamp += span
      operator = :gte

    elsif span >= 1.day && operator == :lte
      # Make sure the timestamps of the given date are included by moving the
      # date to the next date.
      timestamp += span
      operator = :lt
    end
  end

  # return the SQL test
  ["#{field.to_sql(operator, &block)} #{sql_operator(operator, field)} ?", timestamp]
end

#find_field_for_order_by(order, &block) ⇒ Object

Raises:



91
92
93
94
95
96
97
98
 
# File 'lib/scoped_search/query_builder.rb', line 91

def find_field_for_order_by(order, &block)
  order ||= definition.default_order
  return [nil, nil] if order.blank?
  field_name, direction_name = order.to_s.split(/\s+/, 2)
  field = definition.field_by_name(field_name)
  raise ScopedSearch::QueryNotSupported, "the field '#{field_name}' in the order statement is not valid field for search" unless field
  return field, direction_name
end

#find_has_many_through_association(field, through) ⇒ Object 



280
281
282
283
284
285
286
287
288
 
# File 'lib/scoped_search/query_builder.rb', line 280

def find_has_many_through_association(field, through)
  middle_table_association = nil
  field.klass.reflect_on_all_associations(:has_many).each do |reflection|
    class_name = reflection.options[:class_name].constantize.table_name if reflection.options[:class_name]
    middle_table_association = reflection.name if class_name == through.to_s
    middle_table_association = reflection.plural_name if reflection.plural_name == through.to_s
  end
  middle_table_association
end

#has_many_through_join(field) ⇒ Object 



301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
 
# File 'lib/scoped_search/query_builder.rb', line 301

def has_many_through_join(field)
  many_class = field.definition.klass
  connection = many_class.connection
  sql = connection.quote_table_name(many_class.table_name)
  join_reflections = nested_has_many(many_class, field.relation)
  table_names = [[many_class.table_name, many_class.sti_name.tableize]] + join_reflections.map(&:table_name)

  join_reflections.zip(table_names.zip(join_reflections.drop(1))).reduce(sql) do |acc, (reflection, (previous_table, next_reflection))|
    fk1, pk1 = if reflection.respond_to?(:join_keys)
                 klass = reflection.method(:join_keys).arity == 1 ? [reflection.klass] : [] # ActiveRecord <5.2 workaround
                 reflection.join_keys(*klass).values # We are joining the tables "in reverse", so the PK and FK are swapped
               else
                 [reflection.join_primary_key, reflection.join_foreign_key] #ActiveRecord 6.1
               end

    previous_table, sti_name = previous_table
    # primary and foreign keys + optional conditions for the joins
    join_condition = if with_polymorphism?(reflection)
                       field.reflection_conditions(definition.reflection_by_name(next_reflection.klass, sti_name || previous_table))
                     else
                       ''
                     end

    acc + <<-SQL
      INNER JOIN #{connection.quote_table_name(reflection.table_name)}
      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}
    SQL
  end
end

#map_value(field, value) ⇒ Object

Raises:



197
198
199
200
201
202
203
 
# File 'lib/scoped_search/query_builder.rb', line 197

def map_value(field, value)
  old_value = value
  translator = field.value_translation
  value = translator.call(value) if translator
  raise ScopedSearch::QueryNotSupported, "Translation from any value to nil is not allowed, translated '#{old_value}'" if value.nil?
  value
end

#nested_has_many(many_class, relation) ⇒ Object

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



291
292
293
294
295
296
297
298
299
 
# File 'lib/scoped_search/query_builder.rb', line 291

def nested_has_many(many_class, relation)
  acc = [relation]
  while (reflection = definition.reflection_by_name(many_class, relation))
    break if reflection.nil? || reflection.options[:through].nil?
    relation = reflection.options[:through]
    acc.unshift(relation)
  end
  acc.map { |relation| definition.reflection_by_name(many_class, relation) }
end

#order_by(order, &block) ⇒ Object 



100
101
102
103
104
105
106
 
# File 'lib/scoped_search/query_builder.rb', line 100

def order_by(order, &block)
  field, direction_name = find_field_for_order_by(order, &block)
  return nil if field.nil?
  sql = field.to_sql(&block)
  direction = (!direction_name.nil? && direction_name.downcase.eql?('desc')) ? " DESC" : " ASC"
  return sql + direction
end

#preprocess_parameters(field, operator, value, &block) ⇒ Object 



263
264
265
266
267
268
269
270
271
272
 
# File 'lib/scoped_search/query_builder.rb', line 263

def preprocess_parameters(field, operator, value, &block)
  values = if [:in, :notin].include?(operator)
             value.split(',').map { |v| map_value(field, field.set? ? translate_value(field, v) : v.strip) }
           elsif [:like, :unlike].include?(operator)
             [(value !~ /^\%|\*/ && value !~ /\%|\*$/) ? "%#{value}%" : value.tr_s('%*', '%')]
           else
             [map_value(field, field.offset ? value.to_i : value)]
           end
  values.each { |value| yield(:parameter, value) }
end

#set_test(field, operator, value, &block) ⇒ Object

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.

Raises:



207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
 
# File 'lib/scoped_search/query_builder.rb', line 207

def set_test(field, operator,value, &block)
  set_value = translate_value(field, value)
  raise ScopedSearch::QueryNotSupported, "Operator '#{operator}' not supported for '#{field.field}'" unless [:eq,:ne].include?(operator)
  negate = ''
  if [true,false].include?(set_value)
    negate = 'NOT ' if operator == :ne
    if field.numerical?
      operator =  (set_value == true) ?  :gt : :eq
      set_value = 0
    else
      operator = (set_value == true) ? :ne : :eq
      set_value = false
    end
  end
  ["#{negate}(#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} ?)", set_value]
end

#sql_operator(operator, field) ⇒ Object

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.

Raises:



117
118
119
120
 
# File 'lib/scoped_search/query_builder.rb', line 117

def sql_operator(operator, field)
  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?
  SQL_OPERATORS[operator]
end

#sql_test(field, operator, value, lhs) {|:keyparameter, lhs.sub(/^.*\./,'')| ... } ⇒ Object

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.

Yields:

  • (:keyparameter, lhs.sub(/^.*\./,'')) 


232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
 
# File 'lib/scoped_search/query_builder.rb', line 232

def sql_test(field, operator, value, lhs, &block) # :yields: finder_option_type, value
  return field.to_ext_method_sql(lhs, sql_operator(operator, field), value, &block) if field.virtual?

  yield(:keyparameter, lhs.sub(/^.*\./,'')) if field.key_field

  condition, *values = if field.temporal?
                         datetime_test(field, operator, value, &block)
                       elsif field.set?
                         set_test(field, operator, value, &block)
                       else
                         ["#{field.to_sql(operator, &block)} #{self.sql_operator(operator, field)} #{value_placeholders(operator, value)}", value]
                       end
  values.each { |value| preprocess_parameters(field, operator, value, &block) }

  if field.relation && definition.reflection_by_name(field.definition.klass, field.relation).macro == :has_many
    connection = field.definition.klass.connection
    reflection = definition.reflection_by_name(field.definition.klass, field.relation)
    primary_key_col = reflection.options[:primary_key] || field.definition.klass.primary_key
    primary_key = "#{connection.quote_table_name(field.definition.klass.table_name)}.#{connection.quote_column_name(primary_key_col)}"
    key, join_table = if reflection.options.has_key?(:through)
                        [primary_key, has_many_through_join(field)]
                      else
                        [connection.quote_column_name(field.reflection_keys(reflection)[1]),
                         connection.quote_table_name(field.klass.table_name)]
                      end

    condition = "#{primary_key} IN (SELECT #{key} FROM #{join_table} WHERE #{condition} )"
  end
  condition
end

#to_not_sql(rhs, definition, &block) ⇒ Object

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



123
124
125
 
# File 'lib/scoped_search/query_builder.rb', line 123

def to_not_sql(rhs, definition, &block)
  "NOT COALESCE(#{rhs.to_sql(self, definition, &block)}, 0)"
end

#translate_value(field, value) ⇒ Object

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

Raises:



191
192
193
194
195
 
# File 'lib/scoped_search/query_builder.rb', line 191

def translate_value(field, value)
  translated_value = field.complete_value[value.to_sym]
  raise ScopedSearch::QueryNotSupported, "'#{field.field}' should be one of '#{field.complete_value.keys.join(', ')}', but the query was '#{value}'" if translated_value.nil?
  translated_value
end

#value_placeholders(operator, value) ⇒ Object 



274
275
276
277
278
 
# File 'lib/scoped_search/query_builder.rb', line 274

def value_placeholders(operator, value)
  return '?' unless [:in, :notin].include?(operator)

  '(' + value.split(',').map { '?' }.join(',') + ')'
end

#with_polymorphism?(reflection) ⇒ Boolean

Returns:

  • (Boolean) 


331
332
333
334
335
 
# File 'lib/scoped_search/query_builder.rb', line 331

def with_polymorphism?(reflection)
  as = reflection.options[:as]
  return unless as
  definition.reflection_by_name(reflection.klass, as).options[:polymorphic]
end