Class: Query

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

Defined Under Namespace

Classes: StatementInvalid

Constant Summary collapse

@@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'),
]

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(attributes = nil) ⇒ Query

Returns 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

Class Method Details

.add_available_column(column) ⇒ Object 



300
301
302
 
# File 'app/models/query.rb', line 300

def self.add_available_column(column)
  self.available_columns << (column) if column.is_a?(QueryColumn)
end

.available_columns=(v) ⇒ Object 



296
297
298
 
# File 'app/models/query.rb', line 296

def self.available_columns=(v)
  self.available_columns = (v)
end

Instance Method Details

#add_filter(field, operator, values) ⇒ Object 



240
241
242
243
244
245
246
247
248
249
250
251
252
253
 
# File 'app/models/query.rb', line 240

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

#add_filters(fields, operators, values) ⇒ Object

Add multiple filters using add_filter



262
263
264
265
266
267
268
 
# File 'app/models/query.rb', line 262

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

#add_short_filter(field, expression) ⇒ Object 



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

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

#after_initialize ⇒ Object 



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

#available_columns ⇒ Object 



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

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

#available_filters ⇒ Object 



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
225
226
227
228
229
230
231
232
233
234
235
236
237
238
 
# 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
    all_projects = Project.visible.all
    if all_projects.any?
      # members of visible projects
      user_values += User.active.find(:all, :conditions => ["#{User.table_name}.id IN (SELECT DISTINCT user_id FROM members WHERE project_id IN (?))", all_projects.collect(&:id)]).sort.collect{|s| [s.name, s.id.to_s] }
        
      # project filter
      project_values = []
      Project.project_tree(all_projects) do |p, level|
        prefix = (level > 0 ? ('--' * level + ' ') : '')
        project_values << ["#{prefix}#{p.name}", p.id.to_s]
      end
      @available_filters["project_id"] = { :type => :list, :order => 1, :values => project_values} unless project_values.empty?
    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?

  group_values = Group.all.collect {|g| [g.name, g.id.to_s] }
  @available_filters["member_of_group"] = { :type => :list_optional, :order => 6, :values => group_values } unless group_values.empty?

  role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
  @available_filters["assigned_to_role"] = { :type => :list_optional, :order => 7, :values => role_values } unless role_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

#column_names=(names) ⇒ Object 



329
330
331
332
333
334
335
336
337
338
339
 
# File 'app/models/query.rb', line 329

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

#columns ⇒ Object 



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

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

#editable_by?(user) ⇒ Boolean

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

#group_by_column ⇒ Object 



384
385
386
 
# File 'app/models/query.rb', line 384

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

#group_by_sort_order ⇒ Object

Returns the SQL sort order that should be prepended for grouping



371
372
373
374
375
376
377
 
# File 'app/models/query.rb', line 371

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

#group_by_statement ⇒ Object 



388
389
390
 
# File 'app/models/query.rb', line 388

def group_by_statement
  group_by_column.try(:groupable)
end

#groupable_columns ⇒ Object

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



305
306
307
 
# File 'app/models/query.rb', line 305

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

#grouped? ⇒ Boolean

Returns true if the query is a grouped query

Returns:

  • (Boolean) 


380
381
382
 
# File 'app/models/query.rb', line 380

def grouped?
  !group_by_column.nil?
end

#has_column?(column) ⇒ Boolean

Returns:

  • (Boolean) 


341
342
343
 
# File 'app/models/query.rb', line 341

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

#has_default_columns? ⇒ Boolean

Returns:

  • (Boolean) 


345
346
347
 
# File 'app/models/query.rb', line 345

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

#has_filter?(field) ⇒ Boolean

Returns:

  • (Boolean) 


270
271
272
 
# File 'app/models/query.rb', line 270

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

#issue_count ⇒ Object

Returns the issue count



500
501
502
503
504
 
# File 'app/models/query.rb', line 500

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

#issue_count_by_group ⇒ Object

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



507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
 
# File 'app/models/query.rb', line 507

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

#issues(options = {}) ⇒ Object

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



528
529
530
531
532
533
534
535
536
537
538
539
 
# File 'app/models/query.rb', line 528

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

#journals(options = {}) ⇒ Object

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



543
544
545
546
547
548
549
550
551
 
# File 'app/models/query.rb', line 543

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

#label_for(field) ⇒ Object 



282
283
284
285
 
# File 'app/models/query.rb', line 282

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

#operator_for(field) ⇒ Object 



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

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

#project_statement ⇒ Object 



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
 
# File 'app/models/query.rb', line 392

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 <<  Issue.visible_condition(User.current)
  project_clauses.join(' AND ')
end

#sort_criteria ⇒ Object 



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

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

#sort_criteria=(arg) ⇒ Object 



349
350
351
352
353
354
355
356
 
# File 'app/models/query.rb', line 349

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

#sort_criteria_key(arg) ⇒ Object 



362
363
364
 
# File 'app/models/query.rb', line 362

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

#sort_criteria_order(arg) ⇒ Object 



366
367
368
 
# File 'app/models/query.rb', line 366

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

#sortable_columns ⇒ Object

Returns a Hash of columns and the key for sorting



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

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

#statement ⇒ Object 



418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
 
# File 'app/models/query.rb', line 418

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) + ')'
    elsif field == "member_of_group" # named field
      if operator == '*' # Any group
        groups = Group.all
        operator = '=' # Override the operator since we want to find by assigned_to
      elsif operator == "!*"
        groups = Group.all
        operator = '!' # Override the operator since we want to find by assigned_to
      else
        groups = Group.find_all_by_id(v)
      end
      groups ||= []

      members_of_groups = groups.inject([]) {|user_ids, group|
        if group && group.user_ids.present?
          user_ids << group.user_ids
        end
        user_ids.flatten.uniq.compact
      }.sort.collect(&:to_s)
      
      sql << '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'

    elsif field == "assigned_to_role" # named field
      if operator == "*" # Any Role
        roles = Role.givable
        operator = '=' # Override the operator since we want to find by assigned_to
      elsif operator == "!*" # No role
        roles = Role.givable
        operator = '!' # Override the operator since we want to find by assigned_to
      else
        roles = Role.givable.find_all_by_id(v)
      end
      roles ||= []
      
      members_of_roles = roles.inject([]) {|user_ids, role|
        if role && role.members
          user_ids << role.members.collect(&:user_id)
        end
        user_ids.flatten.uniq.compact
      }.sort.collect(&:to_s)
      
      sql << '(' + sql_for_field("assigned_to_id", operator, members_of_roles, Issue.table_name, "assigned_to_id", false) + ')'
    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

#validate ⇒ Object 



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

#values_for(field) ⇒ Object 



278
279
280
 
# File 'app/models/query.rb', line 278

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

#versions(options = {}) ⇒ Object

Returns the versions Valid options are :conditions



555
556
557
558
559
560
 
# File 'app/models/query.rb', line 555

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