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.
15 # +filters+ array of conditions, each being [column, operator, operand]
16 # +model_class+ subclass of ActiveRecord being filtered
20 # :cond_out array of SQL fragments for each filter expression
21 # :param_out array of values for parameter substitution in cond_out
22 def record_filters filters, model_class
26 ar_table_name = model_class.table_name
27 filters.each do |filter|
28 attrs_in, operator, operand = filter
29 if attrs_in == 'any' && operator != '@@'
30 attrs = model_class.searchable_columns(operator)
31 elsif attrs_in.is_a? Array
36 if !filter.is_a? Array
37 raise ArgumentError.new("Invalid element in filters array: #{filter.inspect} is not an array")
38 elsif !operator.is_a? String
39 raise ArgumentError.new("Invalid operator '#{operator}' (#{operator.class}) in filter")
47 raise ArgumentError.new("Full text search on individual columns is not supported")
49 if operand.is_a? Array
50 raise ArgumentError.new("Full text search not supported for array operands")
53 # Skip the generic per-column operator loop below
55 # Use to_tsquery since plainto_tsquery does not support prefix
56 # search. And, split operand and join the words with ' & '
57 cond_out << model_class.full_text_tsvector+" @@ to_tsquery(?)"
58 param_out << operand.split.join(' & ')
61 if !model_class.searchable_columns(operator).index attr.to_s
62 raise ArgumentError.new("Invalid attribute '#{attr}' in filter")
64 case operator.downcase
65 when '=', '<', '<=', '>', '>=', '!=', 'like', 'ilike'
66 attr_type = model_class.attribute_column(attr).type
67 operator = '<>' if operator == '!='
68 if operand.is_a? String
69 if attr_type == :boolean
70 if not ['=', '<>'].include?(operator)
71 raise ArgumentError.new("Invalid operator '#{operator}' for " \
72 "boolean attribute '#{attr}'")
75 when '1', 't', 'true', 'y', 'yes'
77 when '0', 'f', 'false', 'n', 'no'
80 raise ArgumentError("Invalid operand '#{operand}' for " \
81 "boolean attribute '#{attr}'")
85 # explicitly allow NULL
86 cond_out << "#{ar_table_name}.#{attr} #{operator} ? OR #{ar_table_name}.#{attr} IS NULL"
88 cond_out << "#{ar_table_name}.#{attr} #{operator} ?"
90 if (# any operator that operates on value rather than
92 operator.match(/[<=>]/) and (attr_type == :datetime))
93 operand = Time.parse operand
96 elsif operand.nil? and operator == '='
97 cond_out << "#{ar_table_name}.#{attr} is null"
98 elsif operand.nil? and operator == '<>'
99 cond_out << "#{ar_table_name}.#{attr} is not null"
100 elsif (attr_type == :boolean) and ['=', '<>'].include?(operator) and
101 [true, false].include?(operand)
102 cond_out << "#{ar_table_name}.#{attr} #{operator} ?"
105 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
106 "for '#{operator}' operator in filters")
109 if operand.is_a? Array
110 cond_out << "#{ar_table_name}.#{attr} #{operator} (?)"
112 if operator == 'not in' and not operand.include?(nil)
113 # explicitly allow NULL
114 cond_out[-1] = "(#{cond_out[-1]} OR #{ar_table_name}.#{attr} IS NULL)"
117 raise ArgumentError.new("Invalid operand type '#{operand.class}' "\
118 "for '#{operator}' operator in filters")
121 operand = [operand] unless operand.is_a? Array
124 cl = ArvadosModel::kind_class op
126 cond << "#{ar_table_name}.#{attr} like ?"
127 param_out << cl.uuid_like_pattern
132 cond_out << cond.join(' OR ')
134 raise ArgumentError.new("Invalid operator '#{operator}'")
137 conds_out << cond_out.join(' OR ') if cond_out.any?
140 {:cond_out => conds_out, :param_out => param_out}