1 # Mixin module providing a method to convert filters into a list of SQL
2 # fragments suitable to be fed to ActiveRecord #where.
11 # +filters+ array of conditions, each being [column, operator, operand]
12 # +model_class+ subclass of ActiveRecord being filtered
16 # :cond_out array of SQL fragments for each filter expression
17 # :param_out array of values for parameter substitution in cond_out
18 def record_filters filters, model_class
22 ar_table_name = model_class.table_name
23 filters.each do |filter|
24 attrs_in, operator, operand = filter
25 if attrs_in == 'any' && operator != '@@'
26 attrs = model_class.searchable_columns(operator)
27 elsif attrs_in.is_a? Array
32 if !filter.is_a? Array
33 raise ArgumentError.new("Invalid element in filters array: #{filter.inspect} is not an array")
34 elsif !operator.is_a? String
35 raise ArgumentError.new("Invalid operator '#{operator}' (#{operator.class}) in filter")
43 raise ArgumentError.new("Full text search on individual columns is not supported")
45 if operand.is_a? Array
46 raise ArgumentError.new("Full text search not supported for array operands")
49 # Skip the generic per-column operator loop below
51 # Use to_tsquery since plainto_tsquery does not support prefix
52 # search. And, split operand and join the words with ' & '
53 cond_out << model_class.full_text_tsvector+" @@ to_tsquery(?)"
54 param_out << operand.split.join(' & ')
57 if !model_class.searchable_columns(operator).index attr.to_s
58 raise ArgumentError.new("Invalid attribute '#{attr}' in filter")
60 case operator.downcase
61 when '=', '<', '<=', '>', '>=', '!=', 'like', 'ilike'
62 attr_type = model_class.attribute_column(attr).type
63 operator = '<>' if operator == '!='
64 if operand.is_a? String
65 if attr_type == :boolean
66 if not ['=', '<>'].include?(operator)
67 raise ArgumentError.new("Invalid operator '#{operator}' for " \
68 "boolean attribute '#{attr}'")
71 when '1', 't', 'true', 'y', 'yes'
73 when '0', 'f', 'false', 'n', 'no'
76 raise ArgumentError("Invalid operand '#{operand}' for " \
77 "boolean attribute '#{attr}'")
81 # explicitly allow NULL
82 cond_out << "#{ar_table_name}.#{attr} #{operator} ? OR #{ar_table_name}.#{attr} IS NULL"
84 cond_out << "#{ar_table_name}.#{attr} #{operator} ?"
86 if (# any operator that operates on value rather than
88 operator.match(/[<=>]/) and (attr_type == :datetime))
89 operand = Time.parse operand
92 elsif operand.nil? and operator == '='
93 cond_out << "#{ar_table_name}.#{attr} is null"
94 elsif operand.nil? and operator == '<>'
95 cond_out << "#{ar_table_name}.#{attr} is not null"
96 elsif (attr_type == :boolean) and ['=', '<>'].include?(operator) and
97 [true, false].include?(operand)
98 cond_out << "#{ar_table_name}.#{attr} #{operator} ?"
101 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
102 "for '#{operator}' operator in filters")
105 if operand.is_a? Array
106 cond_out << "#{ar_table_name}.#{attr} #{operator} (?)"
108 if operator == 'not in' and not operand.include?(nil)
109 # explicitly allow NULL
110 cond_out[-1] = "(#{cond_out[-1]} OR #{ar_table_name}.#{attr} IS NULL)"
113 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
114 "for '#{operator}' operator in filters")
117 operand = [operand] unless operand.is_a? Array
120 cl = ArvadosModel::kind_class op
122 cond << "#{ar_table_name}.#{attr} like ?"
123 param_out << cl.uuid_like_pattern
128 cond_out << cond.join(' OR ')
131 conds_out << cond_out.join(' OR ') if cond_out.any?
134 {:cond_out => conds_out, :param_out => param_out}