1 # Copyright (C) The Arvados Authors. All rights reserved.
3 # SPDX-License-Identifier: AGPL-3.0
5 # Mixin module providing a method to convert filters into a list of SQL
6 # fragments suitable to be fed to ActiveRecord #where.
18 # +filters+ array of conditions, each being [column, operator, operand]
19 # +model_class+ subclass of ActiveRecord being filtered
23 # :cond_out array of SQL fragments for each filter expression
24 # :param_out array of values for parameter substitution in cond_out
25 def record_filters filters, model_class
29 ar_table_name = model_class.table_name
30 filters.each do |filter|
31 attrs_in, operator, operand = filter
32 if attrs_in == 'any' && operator != '@@'
33 attrs = model_class.searchable_columns(operator)
34 elsif attrs_in.is_a? Array
39 if !filter.is_a? Array
40 raise ArgumentError.new("Invalid element in filters array: #{filter.inspect} is not an array")
41 elsif !operator.is_a? String
42 raise ArgumentError.new("Invalid operator '#{operator}' (#{operator.class}) in filter")
50 raise ArgumentError.new("Full text search on individual columns is not supported")
52 if operand.is_a? Array
53 raise ArgumentError.new("Full text search not supported for array operands")
56 # Skip the generic per-column operator loop below
58 # Use to_tsquery since plainto_tsquery does not support prefix
59 # search. And, split operand and join the words with ' & '
60 cond_out << model_class.full_text_tsvector+" @@ to_tsquery(?)"
61 param_out << operand.split.join(' & ')
64 subproperty = attr.split(".", 2)
66 if !model_class.searchable_columns(operator).index subproperty[0]
67 raise ArgumentError.new("Invalid attribute '#{subproperty[0]}' in filter")
70 if subproperty.length == 2
72 case operator.downcase
74 not_in = if operator.downcase == "!=" then "NOT " else "" end
75 cond_out << "#{not_in}(#{ar_table_name}.#{subproperty[0]} @> ?::jsonb)"
76 param_out << SafeJSON.dump({subproperty[1] => operand})
78 if operand.is_a? Array
80 cond_out << "#{ar_table_name}.#{subproperty[0]} @> ?::jsonb"
81 param_out << SafeJSON.dump({subproperty[1] => opr})
84 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
85 "for '#{operator}' operator in filters")
87 when '<', '<=', '>', '>='
88 cond_out << "#{ar_table_name}.#{subproperty[0]}->? #{operator} ?::jsonb"
89 param_out << subproperty[1]
90 param_out << SafeJSON.dump(operand)
92 cond_out << "#{ar_table_name}.#{subproperty[0]}->>? #{operator} ?"
93 param_out << subproperty[1]
96 if operand.is_a? Array
97 cond_out << "#{ar_table_name}.#{subproperty[0]}->>? NOT IN (?) OR #{ar_table_name}.#{subproperty[0]}->>? IS NULL"
98 param_out << subproperty[1]
100 param_out << subproperty[1]
102 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
103 "for '#{operator}' operator in filters")
107 cond_out << "jsonb_exists(#{ar_table_name}.#{subproperty[0]}, ?)"
109 cond_out << "(NOT jsonb_exists(#{ar_table_name}.#{subproperty[0]}, ?)) OR #{ar_table_name}.#{subproperty[0]} is NULL"
111 param_out << subproperty[1]
113 raise ArgumentError.new("Invalid operator for subproperty search '#{operator}'")
116 case operator.downcase
117 when '=', '<', '<=', '>', '>=', '!=', 'like', 'ilike'
118 attr_type = model_class.attribute_column(attr).type
119 operator = '<>' if operator == '!='
120 if operand.is_a? String
121 if attr_type == :boolean
122 if not ['=', '<>'].include?(operator)
123 raise ArgumentError.new("Invalid operator '#{operator}' for " \
124 "boolean attribute '#{attr}'")
126 case operand.downcase
127 when '1', 't', 'true', 'y', 'yes'
129 when '0', 'f', 'false', 'n', 'no'
132 raise ArgumentError("Invalid operand '#{operand}' for " \
133 "boolean attribute '#{attr}'")
137 # explicitly allow NULL
138 cond_out << "#{ar_table_name}.#{attr} #{operator} ? OR #{ar_table_name}.#{attr} IS NULL"
140 cond_out << "#{ar_table_name}.#{attr} #{operator} ?"
142 if (# any operator that operates on value rather than
144 operator.match(/[<=>]/) and (attr_type == :datetime))
145 operand = Time.parse operand
148 elsif operand.nil? and operator == '='
149 cond_out << "#{ar_table_name}.#{attr} is null"
150 elsif operand.nil? and operator == '<>'
151 cond_out << "#{ar_table_name}.#{attr} is not null"
152 elsif (attr_type == :boolean) and ['=', '<>'].include?(operator) and
153 [true, false].include?(operand)
154 cond_out << "#{ar_table_name}.#{attr} #{operator} ?"
156 elsif (attr_type == :integer)
157 cond_out << "#{ar_table_name}.#{attr} #{operator} ?"
160 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
161 "for '#{operator}' operator in filters")
164 if operand.is_a? Array
165 cond_out << "#{ar_table_name}.#{attr} #{operator} (?)"
167 if operator == 'not in' and not operand.include?(nil)
168 # explicitly allow NULL
169 cond_out[-1] = "(#{cond_out[-1]} OR #{ar_table_name}.#{attr} IS NULL)"
172 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
173 "for '#{operator}' operator in filters")
176 operand = [operand] unless operand.is_a? Array
179 cl = ArvadosModel::kind_class op
181 cond << "#{ar_table_name}.#{attr} like ?"
182 param_out << cl.uuid_like_pattern
187 cond_out << cond.join(' OR ')
189 cond_out << "jsonb_exists(#{ar_table_name}.#{subproperty[0]}, ?)"
192 raise ArgumentError.new("Invalid operator '#{operator}'")
196 conds_out << cond_out.join(' OR ') if cond_out.any?
199 {:cond_out => conds_out, :param_out => param_out}