Class: Query

Inherits:
ActiveRecord::Base show all
Defined in:
app/models/query.rb

Defined Under Namespace

Classes: StatementInvalid

Constant Summary

@@operators =
{ "="   => :label_equals, 
"!"   => :label_not_equals,
"o"   => :label_open_issues,
"c"   => :label_closed_issues,
"!*"  => :label_none,
"*"   => :label_all,
">="  => :label_greater_or_equal,
"<="  => :label_less_or_equal,
"<t+" => :label_in_less_than,
">t+" => :label_in_more_than,
"t+"  => :label_in,
"t"   => :label_today,
"w"   => :label_this_week,
">t-" => :label_less_than_ago,
"<t-" => :label_more_than_ago,
"t-"  => :label_ago,
"~"   => :label_contains,
"!~"  => :label_not_contains }
@@operators_by_filter_type =
{ :list => [ "=", "!" ],
:list_status => [ "o", "=", "!", "c", "*" ],
:list_optional => [ "=", "!", "!*", "*" ],
:list_subprojects => [ "*", "!*", "=" ],
:date => [ "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-" ],
:date_past => [ ">t-", "<t-", "t-", "t", "w" ],
:string => [ "=", "~", "!", "!~" ],
:text => [  "~", "!~" ],
:integer => [ "=", ">=", "<=", "!*", "*" ] }
@@available_columns =
[
  QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
  QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
  QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),
  QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
  QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
  QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
  QueryColumn.new(:author),
  QueryColumn.new(:assigned_to, :sortable => ["#{User.table_name}.lastname", "#{User.table_name}.firstname", "#{User.table_name}.id"], :groupable => true),
  QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
  QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
  QueryColumn.new(:fixed_version, :sortable => ["#{Version.table_name}.effective_date", "#{Version.table_name}.name"], :default_order => 'desc', :groupable => true),
  QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
  QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
  QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),
  QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
  QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
]

Instance Method Summary

Methods inherited from ActiveRecord::Base

quoted_table_name

Constructor Details

- (Query) initialize(attributes = nil)

A new instance of Query



141
142
143
144
# File 'app/models/query.rb', line 141

def initialize(attributes = nil)
  super attributes
  self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
end

Instance Method Details

- (Object) add_filter(field, operator, values)



226
227
228
229
230
231
232
233
234
235
236
237
238
239
# File 'app/models/query.rb', line 226

def add_filter(field, operator, values)
  # values must be an array
  return unless values and values.is_a? Array # and !values.first.empty?
  # check if field is defined as an available filter
  if available_filters.has_key? field
    filter_options = available_filters[field]
    # check if operator is allowed for that filter
    #if @@operators_by_filter_type[filter_options[:type]].include? operator
    #  allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})
    #  filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator
    #end
    filters[field] = {:operator => operator, :values => values }
  end
end

- (Object) add_short_filter(field, expression)



241
242
243
244
245
# File 'app/models/query.rb', line 241

def add_short_filter(field, expression)
  return unless expression
  parms = expression.scan(/^(o|c|!\*|!|\*)?(.*)$/).first
  add_filter field, (parms[0] || "="), [parms[1] || ""]
end

- (Object) after_initialize



146
147
148
149
# File 'app/models/query.rb', line 146

def after_initialize
  # Store the fact that project is nil (used in #editable_by?)
  @is_for_all = project.nil?
end

- (Object) available_columns



264
265
266
267
268
269
270
271
# File 'app/models/query.rb', line 264

def available_columns
  return @available_columns if @available_columns
  @available_columns = Query.available_columns
  @available_columns += (project ? 
                          project.all_issue_custom_fields :
                          IssueCustomField.find(:all)
                         ).collect {|cf| QueryCustomFieldColumn.new(cf) }      
end

- (Object) available_filters



169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
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
# File 'app/models/query.rb', line 169

def available_filters
  return @available_filters if @available_filters
  
  trackers = project.nil? ? Tracker.find(:all, :order => 'position') : project.rolled_up_trackers
  
  @available_filters = { "status_id" => { :type => :list_status, :order => 1, :values => IssueStatus.find(:all, :order => 'position').collect{|s| [s.name, s.id.to_s] } },       
                         "tracker_id" => { :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] } },                                                                                                                
                         "priority_id" => { :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] } },
                         "subject" => { :type => :text, :order => 8 },  
                         "created_on" => { :type => :date_past, :order => 9 },                        
                         "updated_on" => { :type => :date_past, :order => 10 },
                         "start_date" => { :type => :date, :order => 11 },
                         "due_date" => { :type => :date, :order => 12 },
                         "estimated_hours" => { :type => :integer, :order => 13 },
                         "done_ratio" =>  { :type => :integer, :order => 14 }}
  
  user_values = []
  user_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
  if project
    user_values += project.users.sort.collect{|s| [s.name, s.id.to_s] }
  else
    project_ids = User.current.projects.collect(&:id)
    if project_ids.any?
      # members of the user's projects
      user_values += User.active.find(:all, :conditions => ["#{User.table_name}.id IN (SELECT DISTINCT user_id FROM members WHERE project_id IN (?))", project_ids]).sort.collect{|s| [s.name, s.id.to_s] }
    end
  end
  @available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => user_values } unless user_values.empty?
  @available_filters["author_id"] = { :type => :list, :order => 5, :values => user_values } unless user_values.empty?
  
  if User.current.logged?
    @available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }
  end

  if project
    # project specific filters
    unless @project.issue_categories.empty?
      @available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => @project.issue_categories.collect{|s| [s.name, s.id.to_s] } }
    end
    unless @project.shared_versions.empty?
      @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => @project.shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
    end
    unless @project.descendants.active.empty?
      @available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => @project.descendants.visible.collect{|s| [s.name, s.id.to_s] } }
    end
    add_custom_fields_filters(@project.all_issue_custom_fields)
  else
    # global filters for cross project issue list
    system_shared_versions = Version.visible.find_all_by_sharing('system')
    unless system_shared_versions.empty?
      @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => system_shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
    end
    add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))
  end
  @available_filters
end

- (Object) column_names=(names)



298
299
300
301
302
303
304
305
306
307
308
# File 'app/models/query.rb', line 298

def column_names=(names)
  if names
    names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
    names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
    # Set column_names to nil if default columns
    if names.map(&:to_s) == Setting.issue_list_default_columns
      names = nil
    end
  end
  write_attribute(:column_names, names)
end

- (Object) columns



286
287
288
289
290
291
292
293
294
295
296
# File 'app/models/query.rb', line 286

def columns
  if has_default_columns?
    available_columns.select do |c|
      # Adds the project column by default for cross-project lists
      Setting.issue_list_default_columns.include?(c.name.to_s) || (c.name == :project && project.nil?)
    end
  else
    # preserve the column_names order
    column_names.collect {|name| available_columns.find {|col| col.name == name}}.compact
  end
end

- (Boolean) editable_by?(user)

Returns:

  • (Boolean)


161
162
163
164
165
166
167
# File 'app/models/query.rb', line 161

def editable_by?(user)
  return false unless user
  # Admin can edit them all and regular users can edit their private queries
  return true if user.admin? || (!is_public && self.user_id == user.id)
  # Members can not edit public queries that are for all project (only admin is allowed to)
  is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
end

- (Object) group_by_column



353
354
355
# File 'app/models/query.rb', line 353

def group_by_column
  groupable_columns.detect {|c| c.name.to_s == group_by}
end

- (Object) group_by_sort_order

Returns the SQL sort order that should be prepended for grouping



340
341
342
343
344
345
346
# File 'app/models/query.rb', line 340

def group_by_sort_order
  if grouped? && (column = group_by_column)
    column.sortable.is_a?(Array) ?
      column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :
      "#{column.sortable} #{column.default_order}"
  end
end

- (Object) group_by_statement



357
358
359
# File 'app/models/query.rb', line 357

def group_by_statement
  group_by_column.groupable
end

- (Object) groupable_columns

Returns an array of columns that can be used to group the results



274
275
276
# File 'app/models/query.rb', line 274

def groupable_columns
  available_columns.select {|c| c.groupable}
end

- (Boolean) grouped?

Returns true if the query is a grouped query

Returns:

  • (Boolean)


349
350
351
# File 'app/models/query.rb', line 349

def grouped?
  !group_by.blank?
end

- (Boolean) has_column?(column)

Returns:

  • (Boolean)


310
311
312
# File 'app/models/query.rb', line 310

def has_column?(column)
  column_names && column_names.include?(column.name)
end

- (Boolean) has_default_columns?

Returns:

  • (Boolean)


314
315
316
# File 'app/models/query.rb', line 314

def has_default_columns?
  column_names.nil? || column_names.empty?
end

- (Boolean) has_filter?(field)

Returns:

  • (Boolean)


247
248
249
# File 'app/models/query.rb', line 247

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

- (Object) issue_count

Returns the issue count

Raises:



428
429
430
431
432
# File 'app/models/query.rb', line 428

def issue_count
  Issue.count(:include => [:status, :project], :conditions => statement)
rescue ::ActiveRecord::StatementInvalid => e
  raise StatementInvalid.new(e.message)
end

- (Object) issue_count_by_group

Returns the issue count by group or nil if query is not grouped

Raises:



435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
# File 'app/models/query.rb', line 435

def issue_count_by_group
  r = nil
  if grouped?
    begin
      # Rails will raise an (unexpected) RecordNotFound if there's only a nil group value
      r = Issue.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)
    rescue ActiveRecord::RecordNotFound
      r = {nil => issue_count}
    end
    c = group_by_column
    if c.is_a?(QueryCustomFieldColumn)
      r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
    end
  end
  r
rescue ::ActiveRecord::StatementInvalid => e
  raise StatementInvalid.new(e.message)
end

- (Object) issues(options = {})

Returns the issues Valid options are :order, :offset, :limit, :include, :conditions

Raises:



456
457
458
459
460
461
462
463
464
465
466
467
# File 'app/models/query.rb', line 456

def issues(options={})
  order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
  order_option = nil if order_option.blank?
  
  Issue.find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
                   :conditions => Query.merge_conditions(statement, options[:conditions]),
                   :order => order_option,
                   :limit  => options[:limit],
                   :offset => options[:offset]
rescue ::ActiveRecord::StatementInvalid => e
  raise StatementInvalid.new(e.message)
end

- (Object) journals(options = {})

Returns the journals Valid options are :order, :offset, :limit

Raises:



471
472
473
474
475
476
477
478
479
# File 'app/models/query.rb', line 471

def journals(options={})
  Journal.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
                     :conditions => statement,
                     :order => options[:order],
                     :limit => options[:limit],
                     :offset => options[:offset]
rescue ::ActiveRecord::StatementInvalid => e
  raise StatementInvalid.new(e.message)
end

- (Object) label_for(field)



259
260
261
262
# File 'app/models/query.rb', line 259

def label_for(field)
  label = available_filters[field][:name] if available_filters.has_key?(field)
  label ||= field.gsub(/\_id$/, "")
end

- (Object) operator_for(field)



251
252
253
# File 'app/models/query.rb', line 251

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

- (Object) project_statement



361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
# File 'app/models/query.rb', line 361

def project_statement
  project_clauses = []
  if project && !@project.descendants.active.empty?
    ids = [project.id]
    if has_filter?("subproject_id")
      case operator_for("subproject_id")
      when '='
        # include the selected subprojects
        ids += values_for("subproject_id").each(&:to_i)
      when '!*'
        # main project only
      else
        # all subprojects
        ids += project.descendants.collect(&:id)
      end
    elsif Setting.display_subprojects_issues?
      ids += project.descendants.collect(&:id)
    end
    project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
  elsif project
    project_clauses << "#{Project.table_name}.id = %d" % project.id
  end
  project_clauses <<  Project.allowed_to_condition(User.current, :view_issues)
  project_clauses.join(' AND ')
end

- (Object) sort_criteria



327
328
329
# File 'app/models/query.rb', line 327

def sort_criteria
  read_attribute(:sort_criteria) || []
end

- (Object) sort_criteria=(arg)



318
319
320
321
322
323
324
325
# File 'app/models/query.rb', line 318

def sort_criteria=(arg)
  c = []
  if arg.is_a?(Hash)
    arg = arg.keys.sort.collect {|k| arg[k]}
  end
  c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}
  write_attribute(:sort_criteria, c)
end

- (Object) sort_criteria_key(arg)



331
332
333
# File 'app/models/query.rb', line 331

def sort_criteria_key(arg)
  sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
end

- (Object) sort_criteria_order(arg)



335
336
337
# File 'app/models/query.rb', line 335

def sort_criteria_order(arg)
  sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
end

- (Object) sortable_columns

Returns a Hash of columns and the key for sorting



279
280
281
282
283
284
# File 'app/models/query.rb', line 279

def sortable_columns
  {'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|
                                             h[column.name.to_s] = column.sortable
                                             h
                                           })
end

- (Object) statement



387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
# File 'app/models/query.rb', line 387

def statement
  # filters clauses
  filters_clauses = []
  filters.each_key do |field|
    next if field == "subproject_id"
    v = values_for(field).clone
    next unless v and !v.empty?
    operator = operator_for(field)
    
    # "me" value subsitution
    if %w(assigned_to_id author_id watcher_id).include?(field)
      v.push(User.current.logged? ? User.current.id.to_s : "0") if v.delete("me")
    end
    
    sql = ''
    if field =~ /^cf_(\d+)$/
      # custom field
      db_table = CustomValue.table_name
      db_field = 'value'
      is_custom_filter = true
      sql << "#{Issue.table_name}.id IN (SELECT #{Issue.table_name}.id FROM #{Issue.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='Issue' AND #{db_table}.customized_id=#{Issue.table_name}.id AND #{db_table}.custom_field_id=#{$1} WHERE "
      sql << sql_for_field(field, operator, v, db_table, db_field, true) + ')'
    elsif field == 'watcher_id'
      db_table = Watcher.table_name
      db_field = 'user_id'
      sql << "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND "
      sql << sql_for_field(field, '=', v, db_table, db_field) + ')'
    else
      # regular field
      db_table = Issue.table_name
      db_field = field
      sql << '(' + sql_for_field(field, operator, v, db_table, db_field) + ')'
    end
    filters_clauses << sql
    
  end if filters and valid?
  
  (filters_clauses << project_statement).join(' AND ')
end

- (Object) validate



151
152
153
154
155
156
157
158
159
# File 'app/models/query.rb', line 151

def validate
  filters.each_key do |field|
    errors.add label_for(field), :blank unless 
        # filter requires one or more values
        (values_for(field) and !values_for(field).first.blank?) or 
        # filter doesn't require any value
        ["o", "c", "!*", "*", "t", "w"].include? operator_for(field)
  end if filters
end

- (Object) values_for(field)



255
256
257
# File 'app/models/query.rb', line 255

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

- (Object) versions(options = {})

Returns the versions Valid options are :conditions

Raises:



483
484
485
486
487
488
# File 'app/models/query.rb', line 483

def versions(options={})
  Version.find :all, :include => :project,
                     :conditions => Query.merge_conditions(project_statement, options[:conditions])
rescue ::ActiveRecord::StatementInvalid => e
  raise StatementInvalid.new(e.message)
end