##// END OF EJS Templates
Don't add the inclusion error when tracker is not set, the blank error is enough....
Don't add the inclusion error when tracker is not set, the blank error is enough. git-svn-id: http://svn.redmine.org/redmine/trunk@15492 e93f8b46-1217-0410-a6f0-8f06a7374b81

File last commit:

r14989:699a75910db7
r15110:90d14b71b365
Show More
issue_query.rb
565 lines | 23.1 KiB | text/x-ruby | RubyLexer
# Redmine - project management software
# Copyright (C) 2006-2016 Jean-Philippe Lang
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
class IssueQuery < Query
self.queried_class = Issue
self.available_columns = [
QueryColumn.new(:id, :sortable => "#{Issue.table_name}.id", :default_order => 'desc', :caption => '#', :frozen => true),
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, :sortable => lambda {User.fields_for_order_statement("authors")}, :groupable => true),
QueryColumn.new(:assigned_to, :sortable => lambda {User.fields_for_order_statement}, :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 => lambda {Version.fields_for_order_statement}, :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", :totalable => true),
QueryColumn.new(:total_estimated_hours,
:sortable => "COALESCE((SELECT SUM(estimated_hours) FROM #{Issue.table_name} subtasks" +
" WHERE subtasks.root_id = #{Issue.table_name}.root_id AND subtasks.lft >= #{Issue.table_name}.lft AND subtasks.rgt <= #{Issue.table_name}.rgt), 0)",
:default_order => 'desc'),
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'),
QueryColumn.new(:closed_on, :sortable => "#{Issue.table_name}.closed_on", :default_order => 'desc'),
QueryColumn.new(:relations, :caption => :label_related_issues),
QueryColumn.new(:description, :inline => false)
]
scope :visible, lambda {|*args|
user = args.shift || User.current
base = Project.allowed_to_condition(user, :view_issues, *args)
scope = joins("LEFT OUTER JOIN #{Project.table_name} ON #{table_name}.project_id = #{Project.table_name}.id").
where("#{table_name}.project_id IS NULL OR (#{base})")
if user.admin?
scope.where("#{table_name}.visibility <> ? OR #{table_name}.user_id = ?", VISIBILITY_PRIVATE, user.id)
elsif user.memberships.any?
scope.where("#{table_name}.visibility = ?" +
" OR (#{table_name}.visibility = ? AND #{table_name}.id IN (" +
"SELECT DISTINCT q.id FROM #{table_name} q" +
" INNER JOIN #{table_name_prefix}queries_roles#{table_name_suffix} qr on qr.query_id = q.id" +
" INNER JOIN #{MemberRole.table_name} mr ON mr.role_id = qr.role_id" +
" INNER JOIN #{Member.table_name} m ON m.id = mr.member_id AND m.user_id = ?" +
" WHERE q.project_id IS NULL OR q.project_id = m.project_id))" +
" OR #{table_name}.user_id = ?",
VISIBILITY_PUBLIC, VISIBILITY_ROLES, user.id, user.id)
elsif user.logged?
scope.where("#{table_name}.visibility = ? OR #{table_name}.user_id = ?", VISIBILITY_PUBLIC, user.id)
else
scope.where("#{table_name}.visibility = ?", VISIBILITY_PUBLIC)
end
}
def initialize(attributes=nil, *args)
super attributes
self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
end
# Returns true if the query is visible to +user+ or the current user.
def visible?(user=User.current)
return true if user.admin?
return false unless project.nil? || user.allowed_to?(:view_issues, project)
case visibility
when VISIBILITY_PUBLIC
true
when VISIBILITY_ROLES
if project
(user.roles_for_project(project) & roles).any?
else
Member.where(:user_id => user.id).joins(:roles).where(:member_roles => {:role_id => roles.map(&:id)}).any?
end
else
user == self.user
end
end
def is_private?
visibility == VISIBILITY_PRIVATE
end
def is_public?
!is_private?
end
def draw_relations
r = options[:draw_relations]
r.nil? || r == '1'
end
def draw_relations=(arg)
options[:draw_relations] = (arg == '0' ? '0' : nil)
end
def draw_progress_line
r = options[:draw_progress_line]
r == '1'
end
def draw_progress_line=(arg)
options[:draw_progress_line] = (arg == '1' ? '1' : nil)
end
def build_from_params(params)
super
self.draw_relations = params[:draw_relations] || (params[:query] && params[:query][:draw_relations])
self.draw_progress_line = params[:draw_progress_line] || (params[:query] && params[:query][:draw_progress_line])
self
end
def initialize_available_filters
principals = []
subprojects = []
versions = []
categories = []
issue_custom_fields = []
if project
principals += project.principals.visible
unless project.leaf?
subprojects = project.descendants.visible.to_a
principals += Principal.member_of(subprojects).visible
end
versions = project.shared_versions.to_a
categories = project.issue_categories.to_a
issue_custom_fields = project.all_issue_custom_fields
else
if all_projects.any?
principals += Principal.member_of(all_projects).visible
end
versions = Version.visible.where(:sharing => 'system').to_a
issue_custom_fields = IssueCustomField.where(:is_for_all => true)
end
principals.uniq!
principals.sort!
principals.reject! {|p| p.is_a?(GroupBuiltin)}
users = principals.select {|p| p.is_a?(User)}
add_available_filter "status_id",
:type => :list_status, :values => IssueStatus.sorted.collect{|s| [s.name, s.id.to_s] }
if project.nil?
project_values = []
if User.current.logged? && User.current.memberships.any?
project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]
end
project_values += all_projects_values
add_available_filter("project_id",
:type => :list, :values => project_values
) unless project_values.empty?
end
add_available_filter "tracker_id",
:type => :list, :values => trackers.collect{|s| [s.name, s.id.to_s] }
add_available_filter "priority_id",
:type => :list, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] }
author_values = []
author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
author_values += users.collect{|s| [s.name, s.id.to_s] }
add_available_filter("author_id",
:type => :list, :values => author_values
) unless author_values.empty?
assigned_to_values = []
assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
assigned_to_values += (Setting.issue_group_assignment? ?
principals : users).collect{|s| [s.name, s.id.to_s] }
add_available_filter("assigned_to_id",
:type => :list_optional, :values => assigned_to_values
) unless assigned_to_values.empty?
group_values = Group.givable.visible.collect {|g| [g.name, g.id.to_s] }
add_available_filter("member_of_group",
:type => :list_optional, :values => group_values
) unless group_values.empty?
role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
add_available_filter("assigned_to_role",
:type => :list_optional, :values => role_values
) unless role_values.empty?
add_available_filter "fixed_version_id",
:type => :list_optional,
:values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] }
add_available_filter "category_id",
:type => :list_optional,
:values => categories.collect{|s| [s.name, s.id.to_s] }
add_available_filter "subject", :type => :text
add_available_filter "description", :type => :text
add_available_filter "created_on", :type => :date_past
add_available_filter "updated_on", :type => :date_past
add_available_filter "closed_on", :type => :date_past
add_available_filter "start_date", :type => :date
add_available_filter "due_date", :type => :date
add_available_filter "estimated_hours", :type => :float
add_available_filter "done_ratio", :type => :integer
if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
add_available_filter "is_private",
:type => :list,
:values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]]
end
if User.current.logged?
add_available_filter "watcher_id",
:type => :list, :values => [["<< #{l(:label_me)} >>", "me"]]
end
if subprojects.any?
add_available_filter "subproject_id",
:type => :list_subprojects,
:values => subprojects.collect{|s| [s.name, s.id.to_s] }
end
add_custom_fields_filters(issue_custom_fields)
add_associations_custom_fields_filters :project, :author, :assigned_to, :fixed_version
IssueRelation::TYPES.each do |relation_type, options|
add_available_filter relation_type, :type => :relation, :label => options[:name]
end
add_available_filter "parent_id", :type => :tree, :label => :field_parent_issue
add_available_filter "child_id", :type => :tree, :label => :label_subtask_plural
add_available_filter "issue_id", :type => :integer, :label => :label_issue
Tracker.disabled_core_fields(trackers).each {|field|
delete_available_filter field
}
end
def available_columns
return @available_columns if @available_columns
@available_columns = self.class.available_columns.dup
@available_columns += (project ?
project.all_issue_custom_fields :
IssueCustomField
).visible.collect {|cf| QueryCustomFieldColumn.new(cf) }
if User.current.allowed_to?(:view_time_entries, project, :global => true)
index = @available_columns.find_index {|column| column.name == :total_estimated_hours}
index = (index ? index + 1 : -1)
# insert the column after total_estimated_hours or at the end
@available_columns.insert index, QueryColumn.new(:spent_hours,
:sortable => "COALESCE((SELECT SUM(hours) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id), 0)",
:default_order => 'desc',
:caption => :label_spent_time,
:totalable => true
)
@available_columns.insert index+1, QueryColumn.new(:total_spent_hours,
:sortable => "COALESCE((SELECT SUM(hours) FROM #{TimeEntry.table_name} JOIN #{Issue.table_name} subtasks ON subtasks.id = #{TimeEntry.table_name}.issue_id" +
" WHERE subtasks.root_id = #{Issue.table_name}.root_id AND subtasks.lft >= #{Issue.table_name}.lft AND subtasks.rgt <= #{Issue.table_name}.rgt), 0)",
:default_order => 'desc',
:caption => :label_total_spent_time
)
end
if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
@available_columns << QueryColumn.new(:is_private, :sortable => "#{Issue.table_name}.is_private")
end
disabled_fields = Tracker.disabled_core_fields(trackers).map {|field| field.sub(/_id$/, '')}
@available_columns.reject! {|column|
disabled_fields.include?(column.name.to_s)
}
@available_columns
end
def default_columns_names
@default_columns_names ||= begin
default_columns = Setting.issue_list_default_columns.map(&:to_sym)
project.present? ? default_columns : [:project] | default_columns
end
end
def base_scope
Issue.visible.joins(:status, :project).where(statement)
end
# Returns the issue count
def issue_count
base_scope.count
rescue ::ActiveRecord::StatementInvalid => e
raise StatementInvalid.new(e.message)
end
# Returns the issue count by group or nil if query is not grouped
def issue_count_by_group
grouped_query do |scope|
scope.count
end
end
# Returns sum of all the issue's estimated_hours
def total_for_estimated_hours(scope)
map_total(scope.sum(:estimated_hours)) {|t| t.to_f.round(2)}
end
# Returns sum of all the issue's time entries hours
def total_for_spent_hours(scope)
total = if group_by_column.try(:name) == :project
# TODO: remove this when https://github.com/rails/rails/issues/21922 is fixed
# We have to do a custom join without the time_entries.project_id column
# that would trigger a ambiguous column name error
scope.joins("JOIN (SELECT issue_id, hours FROM #{TimeEntry.table_name}) AS joined_time_entries ON joined_time_entries.issue_id = #{Issue.table_name}.id").
sum("joined_time_entries.hours")
else
scope.joins(:time_entries).sum("#{TimeEntry.table_name}.hours")
end
map_total(total) {|t| t.to_f.round(2)}
end
# Returns the issues
# Valid options are :order, :offset, :limit, :include, :conditions
def issues(options={})
order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
scope = Issue.visible.
joins(:status, :project).
where(statement).
includes(([:status, :project] + (options[:include] || [])).uniq).
where(options[:conditions]).
order(order_option).
joins(joins_for_order_statement(order_option.join(','))).
limit(options[:limit]).
offset(options[:offset])
scope = scope.preload(:custom_values)
if has_column?(:author)
scope = scope.preload(:author)
end
issues = scope.to_a
if has_column?(:spent_hours)
Issue.load_visible_spent_hours(issues)
end
if has_column?(:total_spent_hours)
Issue.load_visible_total_spent_hours(issues)
end
if has_column?(:relations)
Issue.load_visible_relations(issues)
end
issues
rescue ::ActiveRecord::StatementInvalid => e
raise StatementInvalid.new(e.message)
end
# Returns the issues ids
def issue_ids(options={})
order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
Issue.visible.
joins(:status, :project).
where(statement).
includes(([:status, :project] + (options[:include] || [])).uniq).
references(([:status, :project] + (options[:include] || [])).uniq).
where(options[:conditions]).
order(order_option).
joins(joins_for_order_statement(order_option.join(','))).
limit(options[:limit]).
offset(options[:offset]).
pluck(:id)
rescue ::ActiveRecord::StatementInvalid => e
raise StatementInvalid.new(e.message)
end
# Returns the journals
# Valid options are :order, :offset, :limit
def journals(options={})
Journal.visible.
joins(:issue => [:project, :status]).
where(statement).
order(options[:order]).
limit(options[:limit]).
offset(options[:offset]).
preload(:details, :user, {:issue => [:project, :author, :tracker, :status]}).
to_a
rescue ::ActiveRecord::StatementInvalid => e
raise StatementInvalid.new(e.message)
end
# Returns the versions
# Valid options are :conditions
def versions(options={})
Version.visible.
where(project_statement).
where(options[:conditions]).
includes(:project).
references(:project).
to_a
rescue ::ActiveRecord::StatementInvalid => e
raise StatementInvalid.new(e.message)
end
def sql_for_watcher_id_field(field, operator, value)
db_table = Watcher.table_name
"#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +
sql_for_field(field, '=', value, db_table, 'user_id') + ')'
end
def sql_for_member_of_group_field(field, operator, value)
if operator == '*' # Any group
groups = Group.givable
operator = '=' # Override the operator since we want to find by assigned_to
elsif operator == "!*"
groups = Group.givable
operator = '!' # Override the operator since we want to find by assigned_to
else
groups = Group.where(:id => value).to_a
end
groups ||= []
members_of_groups = groups.inject([]) {|user_ids, group|
user_ids + group.user_ids + [group.id]
}.uniq.compact.sort.collect(&:to_s)
'(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
end
def sql_for_assigned_to_role_field(field, operator, value)
case operator
when "*", "!*" # Member / Not member
sw = operator == "!*" ? 'NOT' : ''
nl = operator == "!*" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
"(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}" +
" WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id))"
when "=", "!"
role_cond = value.any? ?
"#{MemberRole.table_name}.role_id IN (" + value.collect{|val| "'#{self.class.connection.quote_string(val)}'"}.join(",") + ")" :
"1=0"
sw = operator == "!" ? 'NOT' : ''
nl = operator == "!" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
"(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}, #{MemberRole.table_name}" +
" WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id AND #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}))"
end
end
def sql_for_is_private_field(field, operator, value)
op = (operator == "=" ? 'IN' : 'NOT IN')
va = value.map {|v| v == '0' ? self.class.connection.quoted_false : self.class.connection.quoted_true}.uniq.join(',')
"#{Issue.table_name}.is_private #{op} (#{va})"
end
def sql_for_parent_id_field(field, operator, value)
case operator
when "="
"#{Issue.table_name}.parent_id = #{value.first.to_i}"
when "~"
root_id, lft, rgt = Issue.where(:id => value.first.to_i).pluck(:root_id, :lft, :rgt).first
if root_id && lft && rgt
"#{Issue.table_name}.root_id = #{root_id} AND #{Issue.table_name}.lft > #{lft} AND #{Issue.table_name}.rgt < #{rgt}"
else
"1=0"
end
when "!*"
"#{Issue.table_name}.parent_id IS NULL"
when "*"
"#{Issue.table_name}.parent_id IS NOT NULL"
end
end
def sql_for_child_id_field(field, operator, value)
case operator
when "="
parent_id = Issue.where(:id => value.first.to_i).pluck(:parent_id).first
if parent_id
"#{Issue.table_name}.id = #{parent_id}"
else
"1=0"
end
when "~"
root_id, lft, rgt = Issue.where(:id => value.first.to_i).pluck(:root_id, :lft, :rgt).first
if root_id && lft && rgt
"#{Issue.table_name}.root_id = #{root_id} AND #{Issue.table_name}.lft < #{lft} AND #{Issue.table_name}.rgt > #{rgt}"
else
"1=0"
end
when "!*"
"#{Issue.table_name}.rgt - #{Issue.table_name}.lft = 1"
when "*"
"#{Issue.table_name}.rgt - #{Issue.table_name}.lft > 1"
end
end
def sql_for_issue_id_field(field, operator, value)
ids = value.first.to_s.scan(/\d+/).map(&:to_i).join(",")
if ids.present?
"#{Issue.table_name}.id IN (#{ids})"
else
"1=0"
end
end
def sql_for_relations(field, operator, value, options={})
relation_options = IssueRelation::TYPES[field]
return relation_options unless relation_options
relation_type = field
join_column, target_join_column = "issue_from_id", "issue_to_id"
if relation_options[:reverse] || options[:reverse]
relation_type = relation_options[:reverse] || relation_type
join_column, target_join_column = target_join_column, join_column
end
sql = case operator
when "*", "!*"
op = (operator == "*" ? 'IN' : 'NOT IN')
"#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name} WHERE #{IssueRelation.table_name}.relation_type = '#{self.class.connection.quote_string(relation_type)}')"
when "=", "!"
op = (operator == "=" ? 'IN' : 'NOT IN')
"#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name} WHERE #{IssueRelation.table_name}.relation_type = '#{self.class.connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = #{value.first.to_i})"
when "=p", "=!p", "!p"
op = (operator == "!p" ? 'NOT IN' : 'IN')
comp = (operator == "=!p" ? '<>' : '=')
"#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name}, #{Issue.table_name} relissues WHERE #{IssueRelation.table_name}.relation_type = '#{self.class.connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = relissues.id AND relissues.project_id #{comp} #{value.first.to_i})"
when "*o", "!o"
op = (operator == "!o" ? 'NOT IN' : 'IN')
"#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name}, #{Issue.table_name} relissues WHERE #{IssueRelation.table_name}.relation_type = '#{self.class.connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = relissues.id AND relissues.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_false}))"
end
if relation_options[:sym] == field && !options[:reverse]
sqls = [sql, sql_for_relations(field, operator, value, :reverse => true)]
sql = sqls.join(["!", "!*", "!p"].include?(operator) ? " AND " : " OR ")
end
"(#{sql})"
end
def find_assigned_to_id_filter_values(values)
Principal.visible.where(:id => values).map {|p| [p.name, p.id.to_s]}
end
alias :find_author_id_filter_values :find_assigned_to_id_filter_values
IssueRelation::TYPES.keys.each do |relation_type|
alias_method "sql_for_#{relation_type}_field".to_sym, :sql_for_relations
end
end