Class: TurboFilter::TurboFilterQuery

Inherits:
Object
  • Object
show all
Defined in:
lib/turbo_filter/turbo_filter_query.rb

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(filters_for_class = nil, filters = {}) ⇒ TurboFilterQuery

Returns a new instance of TurboFilterQuery.



51
52
53
54
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 51

def initialize(filters_for_class=nil,filters={})
  @filters_for_class = filters_for_class
  self.filters = filters
end

Class Method Details

.operators_labels ⇒ Object

Returns a hash of localized labels for all filter operators



47
48
49
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 47

def self.operators_labels
  operators.inject({}) {|h, operator| h[operator.first] = I18n.t(*operator.last); h}
end

Instance Method Details

#add_available_filter(field, options) ⇒ Object

Adds an available filter



88
89
90
91
92
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 88

def add_available_filter(field, options)
  @available_filters ||= ActiveSupport::OrderedHash.new
  @available_filters[field] = options
  @available_filters
end

#add_filter(field, operator, values = nil) ⇒ Object 



112
113
114
115
116
117
118
119
120
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 112

def add_filter(field, operator, values=nil)
  # values must be an array
  return unless values.nil? || values.is_a?(Array)
  # check if field is defined as an available filter
  if available_filters.has_key? field
    filter_options = available_filters[field]
    filters[field] = {:operator => operator, :values => (values || [''])}
  end
end

#add_filters(fields, operators, values) ⇒ Object

Add multiple filters using add_filter



133
134
135
136
137
138
139
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 133

def add_filters(fields, operators, values)
  if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
    fields.each do |field|
      add_filter(field, operators[field], values && values[field])
    end
  end
end

#add_short_filter(field, expression) ⇒ Object 



122
123
124
125
126
127
128
129
130
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 122

def add_short_filter(field, expression)
  return unless expression && available_filters.has_key?(field)
  field_type = available_filters[field][:type]
  operators_by_filter_type[field_type].sort.reverse.detect do |operator|
    next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
    values = $1
    add_filter field, operator, values.present? ? values.split('|') : ['']
  end || add_filter(field, '=', expression.split('|'))
end

#available_filters ⇒ Object

Return a hash of available filters



102
103
104
105
106
107
108
109
110
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 102

def available_filters
  unless @available_filters
    initialize_available_filters
    @available_filters.to_a.each do |field, options|
      options[:name] ||= I18n.t("field_#{field}".gsub(/_id$/, ''))
    end
  end
  @available_filters
end

#available_filters_as_json ⇒ Object

Returns a representation of the available filters for JSON serialization



167
168
169
170
171
172
173
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 167

def available_filters_as_json
  json = {}
  available_filters.to_a.each do |field, options|
    json[field] = options.slice(:type, :name, :values).stringify_keys
  end
  json
end

#date_clause(table, field, from, to) ⇒ Object

Returns a SQL clause for a date or datetime field.



313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 313

def date_clause(table, field, from, to)
  s = []
  if from
    if from.is_a?(Date)
      from = Time.local(from.year, from.month, from.day).yesterday.end_of_day
    else
      from = from - 1 # second
    end
    if @filters_for_class.default_timezone == :utc
      from = from.utc
    end
    s << ("#{table}.#{field} > '%s'" % [@filters_for_class.connection.quoted_date(from)])
  end
  if to
    if to.is_a?(Date)
      to = Time.local(to.year, to.month, to.day).end_of_day
    end
    if @filters_for_class.default_timezone == :utc
      to = to.utc
    end
    s << ("#{table}.#{field} <= '%s'" % [@filters_for_class.connection.quoted_date(to)])
  end
  s.join(' AND ')
end

#delete_available_filter(field) ⇒ Object

Removes an available filter



95
96
97
98
99
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 95

def delete_available_filter(field)
  if @available_filters
    @available_filters.delete(field)
  end
end

#has_filter?(field) ⇒ Boolean

Returns:

  • (Boolean) 


141
142
143
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 141

def has_filter?(field)
  filters and filters[field]
end

#label_for(field) ⇒ Object 



161
162
163
164
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 161

def label_for(field)
  label = available_filters[field][:name] if available_filters.has_key?(field)
  label ||= l("field_#{field.to_s.gsub(/_id$/, '')}", :default => field)
end

#operator_for(field) ⇒ Object 



149
150
151
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 149

def operator_for(field)
  has_filter?(field) ? filters[field][:operator] : nil
end

#parse_date(arg) ⇒ Object

Returns a Date or Time from the given filter value



344
345
346
347
348
349
350
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 344

def parse_date(arg)
  if arg.to_s =~ /\A\d{4}-\d{2}-\d{2}T/
    Time.parse(arg) rescue nil
  else
    Date.parse(arg) rescue nil
  end
end

#relative_date_clause(table, field, days_from, days_to) ⇒ Object

Returns a SQL clause for a date or datetime field using relative dates.



339
340
341
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 339

def relative_date_clause(table, field, days_from, days_to)
  date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
end

#sql_for_field(field, operator, value, db_table, db_field) ⇒ Object

Helper method to generate the WHERE sql for a field, operator and a value



193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
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
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 193

def sql_for_field(field, operator, value, db_table, db_field)
  sql = ''
  case operator
  when "="
    if value.any?
      case type_for(field)
      when :date, :date_past
        sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first))
      when :integer
        sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
      when :float
        sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
      else
        sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{@filters_for_class.connection.quote_string(val)}'"}.join(",") + ")"
      end
    else
      # IN an empty set
      sql = "1=0"
    end
  when "!"
    if value.any?
      sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{@filters_for_class.connection.quote_string(val)}'"}.join(",") + "))"
    else
      # NOT IN an empty set
      sql = "1=1"
    end
  when "!*"
    sql = "#{db_table}.#{db_field} IS NULL"
  when "*"
    sql = "#{db_table}.#{db_field} IS NOT NULL"
  when ">="
    if [:date, :date_past].include?(type_for(field))
      sql = date_clause(db_table, db_field, parse_date(value.first), nil)
    else
      sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
    end
  when "<="
    if [:date, :date_past].include?(type_for(field))
      sql = date_clause(db_table, db_field, nil, parse_date(value.first))
    else
      sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
    end
  when "><"
    if [:date, :date_past].include?(type_for(field))
      sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]))
    else
      sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
    end
  when "><t-"
    # between today - n days and today
    sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
  when ">t-"
    # >= today - n days
    sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil)
  when "<t-"
    # <= today - n days
    sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
  when "t-"
    # = n days in past
    sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
  when "><t+"
    # between today and today + n days
    sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
  when ">t+"
    # >= today + n days
    sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
  when "<t+"
    # <= today + n days
    sql = relative_date_clause(db_table, db_field, nil, value.first.to_i)
  when "t+"
    # = today + n days
    sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
  when "t"
    # = today
    sql = relative_date_clause(db_table, db_field, 0, 0)
  when "ld"
    # = yesterday
    sql = relative_date_clause(db_table, db_field, -1, -1)
  when "w"
    # = this week
    first_day_of_week = l(:general_first_day_of_week).to_i
    day_of_week = Date.today.cwday
    days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
    sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
  when "lw"
    # = last week
    first_day_of_week = l(:general_first_day_of_week).to_i
    day_of_week = Date.today.cwday
    days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
    sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1)
  when "l2w"
    # = last 2 weeks
    first_day_of_week = l(:general_first_day_of_week).to_i
    day_of_week = Date.today.cwday
    days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
    sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1)
  when "m"
    # = this month
    date = Date.today
    sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)
  when "lm"
    # = last month
    date = Date.today.prev_month
    sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)
  when "y"
    # = this year
    date = Date.today
    sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year)
  when "~"
    sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{@filters_for_class.connection.quote_string(value.first.to_s.downcase)}%'"
  when "!~"
    sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{@filters_for_class.connection.quote_string(value.first.to_s.downcase)}%'"
  else
    raise "Unknown query operator #{operator}"
  end

  return sql
end

#statement ⇒ Object 



175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 175

def statement
  # filters clauses
  filters_clauses = []
  filters.each_key do |field|
    v = values_for(field).clone
    next unless v and !v.empty?
    operator = operator_for(field)

    filters_clauses << '(' + sql_for_field(field, operator, v, @filters_for_class.table_name, field) + ')'
  end if filters

  filters_clauses.reject!(&:blank?)

  filters_clauses.any? ? filters_clauses.join(' AND ') : nil
end

#type_for(field) ⇒ Object 



145
146
147
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 145

def type_for(field)
  available_filters[field][:type] if available_filters.has_key?(field)
end

#value_for(field, index = 0) ⇒ Object 



157
158
159
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 157

def value_for(field, index=0)
  (values_for(field) || [])[index]
end

#values_for(field) ⇒ Object 



153
154
155
 
# File 'lib/turbo_filter/turbo_filter_query.rb', line 153

def values_for(field)
  has_filter?(field) ? filters[field][:values] : nil
end