##// END OF EJS Templates
jro_apps » redmine
RSS
Prevent sqlserver adapter from breaking the sub-query (#12713)....
Jean-Philippe Lang -
r10886:9a66463ff846
parent child
Show More
Side by Side Unified
Context file:
r10886:9a66463ff846 Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,423 +1,423
1 # Redmine - project management software
1 # Redmine - project management software
2 # Copyright (C) 2006-2012 Jean-Philippe Lang
2 # Copyright (C) 2006-2012 Jean-Philippe Lang
3 #
3 #
4 # This program is free software; you can redistribute it and/or
4 # This program is free software; you can redistribute it and/or
5 # modify it under the terms of the GNU General Public License
5 # modify it under the terms of the GNU General Public License
6 # as published by the Free Software Foundation; either version 2
6 # as published by the Free Software Foundation; either version 2
7 # of the License, or (at your option) any later version.
7 # of the License, or (at your option) any later version.
8 #
8 #
9 # This program is distributed in the hope that it will be useful,
9 # This program is distributed in the hope that it will be useful,
10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 # GNU General Public License for more details.
12 # GNU General Public License for more details.
13 #
13 #
14 # You should have received a copy of the GNU General Public License
14 # You should have received a copy of the GNU General Public License
15 # along with this program; if not, write to the Free Software
15 # along with this program; if not, write to the Free Software
16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17
17
18 class IssueQuery < Query
18 class IssueQuery < Query
19
19
20 self.queried_class = Issue
20 self.queried_class = Issue
21
21
22 self.available_columns = [
22 self.available_columns = [
23 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
23 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
24 QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
24 QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
25 QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),
25 QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),
26 QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
26 QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
27 QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
27 QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
28 QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
28 QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
29 QueryColumn.new(:author, :sortable => lambda {User.fields_for_order_statement("authors")}, :groupable => true),
29 QueryColumn.new(:author, :sortable => lambda {User.fields_for_order_statement("authors")}, :groupable => true),
30 QueryColumn.new(:assigned_to, :sortable => lambda {User.fields_for_order_statement}, :groupable => true),
30 QueryColumn.new(:assigned_to, :sortable => lambda {User.fields_for_order_statement}, :groupable => true),
31 QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
31 QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
32 QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
32 QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
33 QueryColumn.new(:fixed_version, :sortable => lambda {Version.fields_for_order_statement}, :groupable => true),
33 QueryColumn.new(:fixed_version, :sortable => lambda {Version.fields_for_order_statement}, :groupable => true),
34 QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
34 QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
35 QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
35 QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
36 QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),
36 QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),
37 QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
37 QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
38 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
38 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
39 QueryColumn.new(:relations, :caption => :label_related_issues),
39 QueryColumn.new(:relations, :caption => :label_related_issues),
40 QueryColumn.new(:description, :inline => false)
40 QueryColumn.new(:description, :inline => false)
41 ]
41 ]
42
42
43 scope :visible, lambda {|*args|
43 scope :visible, lambda {|*args|
44 user = args.shift || User.current
44 user = args.shift || User.current
45 base = Project.allowed_to_condition(user, :view_issues, *args)
45 base = Project.allowed_to_condition(user, :view_issues, *args)
46 user_id = user.logged? ? user.id : 0
46 user_id = user.logged? ? user.id : 0
47
47
48 includes(:project).where("(#{table_name}.project_id IS NULL OR (#{base})) AND (#{table_name}.is_public = ? OR #{table_name}.user_id = ?)", true, user_id)
48 includes(:project).where("(#{table_name}.project_id IS NULL OR (#{base})) AND (#{table_name}.is_public = ? OR #{table_name}.user_id = ?)", true, user_id)
49 }
49 }
50
50
51 def initialize(attributes=nil, *args)
51 def initialize(attributes=nil, *args)
52 super attributes
52 super attributes
53 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
53 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
54 end
54 end
55
55
56 # Returns true if the query is visible to +user+ or the current user.
56 # Returns true if the query is visible to +user+ or the current user.
57 def visible?(user=User.current)
57 def visible?(user=User.current)
58 (project.nil? || user.allowed_to?(:view_issues, project)) && (self.is_public? || self.user_id == user.id)
58 (project.nil? || user.allowed_to?(:view_issues, project)) && (self.is_public? || self.user_id == user.id)
59 end
59 end
60
60
61 def available_filters
61 def available_filters
62 return @available_filters if @available_filters
62 return @available_filters if @available_filters
63 @available_filters = {
63 @available_filters = {
64 "status_id" => {
64 "status_id" => {
65 :type => :list_status, :order => 0,
65 :type => :list_status, :order => 0,
66 :values => IssueStatus.sorted.all.collect{|s| [s.name, s.id.to_s] }
66 :values => IssueStatus.sorted.all.collect{|s| [s.name, s.id.to_s] }
67 },
67 },
68 "tracker_id" => {
68 "tracker_id" => {
69 :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] }
69 :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] }
70 },
70 },
71 "priority_id" => {
71 "priority_id" => {
72 :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] }
72 :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] }
73 },
73 },
74 "subject" => { :type => :text, :order => 8 },
74 "subject" => { :type => :text, :order => 8 },
75 "created_on" => { :type => :date_past, :order => 9 },
75 "created_on" => { :type => :date_past, :order => 9 },
76 "updated_on" => { :type => :date_past, :order => 10 },
76 "updated_on" => { :type => :date_past, :order => 10 },
77 "start_date" => { :type => :date, :order => 11 },
77 "start_date" => { :type => :date, :order => 11 },
78 "due_date" => { :type => :date, :order => 12 },
78 "due_date" => { :type => :date, :order => 12 },
79 "estimated_hours" => { :type => :float, :order => 13 },
79 "estimated_hours" => { :type => :float, :order => 13 },
80 "done_ratio" => { :type => :integer, :order => 14 }
80 "done_ratio" => { :type => :integer, :order => 14 }
81 }
81 }
82 IssueRelation::TYPES.each do |relation_type, options|
82 IssueRelation::TYPES.each do |relation_type, options|
83 @available_filters[relation_type] = {
83 @available_filters[relation_type] = {
84 :type => :relation, :order => @available_filters.size + 100,
84 :type => :relation, :order => @available_filters.size + 100,
85 :label => options[:name]
85 :label => options[:name]
86 }
86 }
87 end
87 end
88 principals = []
88 principals = []
89 if project
89 if project
90 principals += project.principals.sort
90 principals += project.principals.sort
91 unless project.leaf?
91 unless project.leaf?
92 subprojects = project.descendants.visible.all
92 subprojects = project.descendants.visible.all
93 if subprojects.any?
93 if subprojects.any?
94 @available_filters["subproject_id"] = {
94 @available_filters["subproject_id"] = {
95 :type => :list_subprojects, :order => 13,
95 :type => :list_subprojects, :order => 13,
96 :values => subprojects.collect{|s| [s.name, s.id.to_s] }
96 :values => subprojects.collect{|s| [s.name, s.id.to_s] }
97 }
97 }
98 principals += Principal.member_of(subprojects)
98 principals += Principal.member_of(subprojects)
99 end
99 end
100 end
100 end
101 else
101 else
102 if all_projects.any?
102 if all_projects.any?
103 # members of visible projects
103 # members of visible projects
104 principals += Principal.member_of(all_projects)
104 principals += Principal.member_of(all_projects)
105 # project filter
105 # project filter
106 project_values = []
106 project_values = []
107 if User.current.logged? && User.current.memberships.any?
107 if User.current.logged? && User.current.memberships.any?
108 project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]
108 project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]
109 end
109 end
110 project_values += all_projects_values
110 project_values += all_projects_values
111 @available_filters["project_id"] = {
111 @available_filters["project_id"] = {
112 :type => :list, :order => 1, :values => project_values
112 :type => :list, :order => 1, :values => project_values
113 } unless project_values.empty?
113 } unless project_values.empty?
114 end
114 end
115 end
115 end
116 principals.uniq!
116 principals.uniq!
117 principals.sort!
117 principals.sort!
118 users = principals.select {|p| p.is_a?(User)}
118 users = principals.select {|p| p.is_a?(User)}
119
119
120 assigned_to_values = []
120 assigned_to_values = []
121 assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
121 assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
122 assigned_to_values += (Setting.issue_group_assignment? ?
122 assigned_to_values += (Setting.issue_group_assignment? ?
123 principals : users).collect{|s| [s.name, s.id.to_s] }
123 principals : users).collect{|s| [s.name, s.id.to_s] }
124 @available_filters["assigned_to_id"] = {
124 @available_filters["assigned_to_id"] = {
125 :type => :list_optional, :order => 4, :values => assigned_to_values
125 :type => :list_optional, :order => 4, :values => assigned_to_values
126 } unless assigned_to_values.empty?
126 } unless assigned_to_values.empty?
127
127
128 author_values = []
128 author_values = []
129 author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
129 author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
130 author_values += users.collect{|s| [s.name, s.id.to_s] }
130 author_values += users.collect{|s| [s.name, s.id.to_s] }
131 @available_filters["author_id"] = {
131 @available_filters["author_id"] = {
132 :type => :list, :order => 5, :values => author_values
132 :type => :list, :order => 5, :values => author_values
133 } unless author_values.empty?
133 } unless author_values.empty?
134
134
135 group_values = Group.all.collect {|g| [g.name, g.id.to_s] }
135 group_values = Group.all.collect {|g| [g.name, g.id.to_s] }
136 @available_filters["member_of_group"] = {
136 @available_filters["member_of_group"] = {
137 :type => :list_optional, :order => 6, :values => group_values
137 :type => :list_optional, :order => 6, :values => group_values
138 } unless group_values.empty?
138 } unless group_values.empty?
139
139
140 role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
140 role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
141 @available_filters["assigned_to_role"] = {
141 @available_filters["assigned_to_role"] = {
142 :type => :list_optional, :order => 7, :values => role_values
142 :type => :list_optional, :order => 7, :values => role_values
143 } unless role_values.empty?
143 } unless role_values.empty?
144
144
145 if User.current.logged?
145 if User.current.logged?
146 @available_filters["watcher_id"] = {
146 @available_filters["watcher_id"] = {
147 :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]]
147 :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]]
148 }
148 }
149 end
149 end
150
150
151 if project
151 if project
152 # project specific filters
152 # project specific filters
153 categories = project.issue_categories.all
153 categories = project.issue_categories.all
154 unless categories.empty?
154 unless categories.empty?
155 @available_filters["category_id"] = {
155 @available_filters["category_id"] = {
156 :type => :list_optional, :order => 6,
156 :type => :list_optional, :order => 6,
157 :values => categories.collect{|s| [s.name, s.id.to_s] }
157 :values => categories.collect{|s| [s.name, s.id.to_s] }
158 }
158 }
159 end
159 end
160 versions = project.shared_versions.all
160 versions = project.shared_versions.all
161 unless versions.empty?
161 unless versions.empty?
162 @available_filters["fixed_version_id"] = {
162 @available_filters["fixed_version_id"] = {
163 :type => :list_optional, :order => 7,
163 :type => :list_optional, :order => 7,
164 :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] }
164 :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] }
165 }
165 }
166 end
166 end
167 add_custom_fields_filters(project.all_issue_custom_fields)
167 add_custom_fields_filters(project.all_issue_custom_fields)
168 else
168 else
169 # global filters for cross project issue list
169 # global filters for cross project issue list
170 system_shared_versions = Version.visible.find_all_by_sharing('system')
170 system_shared_versions = Version.visible.find_all_by_sharing('system')
171 unless system_shared_versions.empty?
171 unless system_shared_versions.empty?
172 @available_filters["fixed_version_id"] = {
172 @available_filters["fixed_version_id"] = {
173 :type => :list_optional, :order => 7,
173 :type => :list_optional, :order => 7,
174 :values => system_shared_versions.sort.collect{|s|
174 :values => system_shared_versions.sort.collect{|s|
175 ["#{s.project.name} - #{s.name}", s.id.to_s]
175 ["#{s.project.name} - #{s.name}", s.id.to_s]
176 }
176 }
177 }
177 }
178 end
178 end
179 add_custom_fields_filters(IssueCustomField.where(:is_filter => true, :is_for_all => true).all)
179 add_custom_fields_filters(IssueCustomField.where(:is_filter => true, :is_for_all => true).all)
180 end
180 end
181 add_associations_custom_fields_filters :project, :author, :assigned_to, :fixed_version
181 add_associations_custom_fields_filters :project, :author, :assigned_to, :fixed_version
182 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
182 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
183 User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
183 User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
184 @available_filters["is_private"] = {
184 @available_filters["is_private"] = {
185 :type => :list, :order => 16,
185 :type => :list, :order => 16,
186 :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]]
186 :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]]
187 }
187 }
188 end
188 end
189 Tracker.disabled_core_fields(trackers).each {|field|
189 Tracker.disabled_core_fields(trackers).each {|field|
190 @available_filters.delete field
190 @available_filters.delete field
191 }
191 }
192 @available_filters.each do |field, options|
192 @available_filters.each do |field, options|
193 options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))
193 options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))
194 end
194 end
195 @available_filters
195 @available_filters
196 end
196 end
197
197
198 def available_columns
198 def available_columns
199 return @available_columns if @available_columns
199 return @available_columns if @available_columns
200 @available_columns = self.class.available_columns.dup
200 @available_columns = self.class.available_columns.dup
201 @available_columns += (project ?
201 @available_columns += (project ?
202 project.all_issue_custom_fields :
202 project.all_issue_custom_fields :
203 IssueCustomField.all
203 IssueCustomField.all
204 ).collect {|cf| QueryCustomFieldColumn.new(cf) }
204 ).collect {|cf| QueryCustomFieldColumn.new(cf) }
205
205
206 if User.current.allowed_to?(:view_time_entries, project, :global => true)
206 if User.current.allowed_to?(:view_time_entries, project, :global => true)
207 index = nil
207 index = nil
208 @available_columns.each_with_index {|column, i| index = i if column.name == :estimated_hours}
208 @available_columns.each_with_index {|column, i| index = i if column.name == :estimated_hours}
209 index = (index ? index + 1 : -1)
209 index = (index ? index + 1 : -1)
210 # insert the column after estimated_hours or at the end
210 # insert the column after estimated_hours or at the end
211 @available_columns.insert index, QueryColumn.new(:spent_hours,
211 @available_columns.insert index, QueryColumn.new(:spent_hours,
212 :sortable => "(SELECT COALESCE(SUM(hours), 0) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id)",
212 :sortable => "COALESCE((SELECT SUM(hours) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id), 0)",
213 :default_order => 'desc',
213 :default_order => 'desc',
214 :caption => :label_spent_time
214 :caption => :label_spent_time
215 )
215 )
216 end
216 end
217
217
218 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
218 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
219 User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
219 User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
220 @available_columns << QueryColumn.new(:is_private, :sortable => "#{Issue.table_name}.is_private")
220 @available_columns << QueryColumn.new(:is_private, :sortable => "#{Issue.table_name}.is_private")
221 end
221 end
222
222
223 disabled_fields = Tracker.disabled_core_fields(trackers).map {|field| field.sub(/_id$/, '')}
223 disabled_fields = Tracker.disabled_core_fields(trackers).map {|field| field.sub(/_id$/, '')}
224 @available_columns.reject! {|column|
224 @available_columns.reject! {|column|
225 disabled_fields.include?(column.name.to_s)
225 disabled_fields.include?(column.name.to_s)
226 }
226 }
227
227
228 @available_columns
228 @available_columns
229 end
229 end
230
230
231 def sortable_columns
231 def sortable_columns
232 {'id' => "#{Issue.table_name}.id"}.merge(super)
232 {'id' => "#{Issue.table_name}.id"}.merge(super)
233 end
233 end
234
234
235 def default_columns_names
235 def default_columns_names
236 @default_columns_names ||= begin
236 @default_columns_names ||= begin
237 default_columns = Setting.issue_list_default_columns.map(&:to_sym)
237 default_columns = Setting.issue_list_default_columns.map(&:to_sym)
238
238
239 project.present? ? default_columns : [:project] | default_columns
239 project.present? ? default_columns : [:project] | default_columns
240 end
240 end
241 end
241 end
242
242
243 # Returns the issue count
243 # Returns the issue count
244 def issue_count
244 def issue_count
245 Issue.visible.count(:include => [:status, :project], :conditions => statement)
245 Issue.visible.count(:include => [:status, :project], :conditions => statement)
246 rescue ::ActiveRecord::StatementInvalid => e
246 rescue ::ActiveRecord::StatementInvalid => e
247 raise StatementInvalid.new(e.message)
247 raise StatementInvalid.new(e.message)
248 end
248 end
249
249
250 # Returns the issue count by group or nil if query is not grouped
250 # Returns the issue count by group or nil if query is not grouped
251 def issue_count_by_group
251 def issue_count_by_group
252 r = nil
252 r = nil
253 if grouped?
253 if grouped?
254 begin
254 begin
255 # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
255 # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
256 r = Issue.visible.count(:joins => joins_for_order_statement(group_by_statement), :group => group_by_statement, :include => [:status, :project], :conditions => statement)
256 r = Issue.visible.count(:joins => joins_for_order_statement(group_by_statement), :group => group_by_statement, :include => [:status, :project], :conditions => statement)
257 rescue ActiveRecord::RecordNotFound
257 rescue ActiveRecord::RecordNotFound
258 r = {nil => issue_count}
258 r = {nil => issue_count}
259 end
259 end
260 c = group_by_column
260 c = group_by_column
261 if c.is_a?(QueryCustomFieldColumn)
261 if c.is_a?(QueryCustomFieldColumn)
262 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
262 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
263 end
263 end
264 end
264 end
265 r
265 r
266 rescue ::ActiveRecord::StatementInvalid => e
266 rescue ::ActiveRecord::StatementInvalid => e
267 raise StatementInvalid.new(e.message)
267 raise StatementInvalid.new(e.message)
268 end
268 end
269
269
270 # Returns the issues
270 # Returns the issues
271 # Valid options are :order, :offset, :limit, :include, :conditions
271 # Valid options are :order, :offset, :limit, :include, :conditions
272 def issues(options={})
272 def issues(options={})
273 order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
273 order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
274
274
275 issues = Issue.visible.where(options[:conditions]).all(
275 issues = Issue.visible.where(options[:conditions]).all(
276 :include => ([:status, :project] + (options[:include] || [])).uniq,
276 :include => ([:status, :project] + (options[:include] || [])).uniq,
277 :conditions => statement,
277 :conditions => statement,
278 :order => order_option,
278 :order => order_option,
279 :joins => joins_for_order_statement(order_option.join(',')),
279 :joins => joins_for_order_statement(order_option.join(',')),
280 :limit => options[:limit],
280 :limit => options[:limit],
281 :offset => options[:offset]
281 :offset => options[:offset]
282 )
282 )
283
283
284 if has_column?(:spent_hours)
284 if has_column?(:spent_hours)
285 Issue.load_visible_spent_hours(issues)
285 Issue.load_visible_spent_hours(issues)
286 end
286 end
287 if has_column?(:relations)
287 if has_column?(:relations)
288 Issue.load_visible_relations(issues)
288 Issue.load_visible_relations(issues)
289 end
289 end
290 issues
290 issues
291 rescue ::ActiveRecord::StatementInvalid => e
291 rescue ::ActiveRecord::StatementInvalid => e
292 raise StatementInvalid.new(e.message)
292 raise StatementInvalid.new(e.message)
293 end
293 end
294
294
295 # Returns the issues ids
295 # Returns the issues ids
296 def issue_ids(options={})
296 def issue_ids(options={})
297 order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
297 order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
298
298
299 Issue.visible.scoped(:conditions => options[:conditions]).scoped(:include => ([:status, :project] + (options[:include] || [])).uniq,
299 Issue.visible.scoped(:conditions => options[:conditions]).scoped(:include => ([:status, :project] + (options[:include] || [])).uniq,
300 :conditions => statement,
300 :conditions => statement,
301 :order => order_option,
301 :order => order_option,
302 :joins => joins_for_order_statement(order_option.join(',')),
302 :joins => joins_for_order_statement(order_option.join(',')),
303 :limit => options[:limit],
303 :limit => options[:limit],
304 :offset => options[:offset]).find_ids
304 :offset => options[:offset]).find_ids
305 rescue ::ActiveRecord::StatementInvalid => e
305 rescue ::ActiveRecord::StatementInvalid => e
306 raise StatementInvalid.new(e.message)
306 raise StatementInvalid.new(e.message)
307 end
307 end
308
308
309 # Returns the journals
309 # Returns the journals
310 # Valid options are :order, :offset, :limit
310 # Valid options are :order, :offset, :limit
311 def journals(options={})
311 def journals(options={})
312 Journal.visible.all(
312 Journal.visible.all(
313 :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
313 :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
314 :conditions => statement,
314 :conditions => statement,
315 :order => options[:order],
315 :order => options[:order],
316 :limit => options[:limit],
316 :limit => options[:limit],
317 :offset => options[:offset]
317 :offset => options[:offset]
318 )
318 )
319 rescue ::ActiveRecord::StatementInvalid => e
319 rescue ::ActiveRecord::StatementInvalid => e
320 raise StatementInvalid.new(e.message)
320 raise StatementInvalid.new(e.message)
321 end
321 end
322
322
323 # Returns the versions
323 # Returns the versions
324 # Valid options are :conditions
324 # Valid options are :conditions
325 def versions(options={})
325 def versions(options={})
326 Version.visible.where(options[:conditions]).all(
326 Version.visible.where(options[:conditions]).all(
327 :include => :project,
327 :include => :project,
328 :conditions => project_statement
328 :conditions => project_statement
329 )
329 )
330 rescue ::ActiveRecord::StatementInvalid => e
330 rescue ::ActiveRecord::StatementInvalid => e
331 raise StatementInvalid.new(e.message)
331 raise StatementInvalid.new(e.message)
332 end
332 end
333
333
334 def sql_for_watcher_id_field(field, operator, value)
334 def sql_for_watcher_id_field(field, operator, value)
335 db_table = Watcher.table_name
335 db_table = Watcher.table_name
336 "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +
336 "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +
337 sql_for_field(field, '=', value, db_table, 'user_id') + ')'
337 sql_for_field(field, '=', value, db_table, 'user_id') + ')'
338 end
338 end
339
339
340 def sql_for_member_of_group_field(field, operator, value)
340 def sql_for_member_of_group_field(field, operator, value)
341 if operator == '*' # Any group
341 if operator == '*' # Any group
342 groups = Group.all
342 groups = Group.all
343 operator = '=' # Override the operator since we want to find by assigned_to
343 operator = '=' # Override the operator since we want to find by assigned_to
344 elsif operator == "!*"
344 elsif operator == "!*"
345 groups = Group.all
345 groups = Group.all
346 operator = '!' # Override the operator since we want to find by assigned_to
346 operator = '!' # Override the operator since we want to find by assigned_to
347 else
347 else
348 groups = Group.find_all_by_id(value)
348 groups = Group.find_all_by_id(value)
349 end
349 end
350 groups ||= []
350 groups ||= []
351
351
352 members_of_groups = groups.inject([]) {|user_ids, group|
352 members_of_groups = groups.inject([]) {|user_ids, group|
353 if group && group.user_ids.present?
353 if group && group.user_ids.present?
354 user_ids << group.user_ids
354 user_ids << group.user_ids
355 end
355 end
356 user_ids.flatten.uniq.compact
356 user_ids.flatten.uniq.compact
357 }.sort.collect(&:to_s)
357 }.sort.collect(&:to_s)
358
358
359 '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
359 '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
360 end
360 end
361
361
362 def sql_for_assigned_to_role_field(field, operator, value)
362 def sql_for_assigned_to_role_field(field, operator, value)
363 case operator
363 case operator
364 when "*", "!*" # Member / Not member
364 when "*", "!*" # Member / Not member
365 sw = operator == "!*" ? 'NOT' : ''
365 sw = operator == "!*" ? 'NOT' : ''
366 nl = operator == "!*" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
366 nl = operator == "!*" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
367 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}" +
367 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}" +
368 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id))"
368 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id))"
369 when "=", "!"
369 when "=", "!"
370 role_cond = value.any? ?
370 role_cond = value.any? ?
371 "#{MemberRole.table_name}.role_id IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")" :
371 "#{MemberRole.table_name}.role_id IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")" :
372 "1=0"
372 "1=0"
373
373
374 sw = operator == "!" ? 'NOT' : ''
374 sw = operator == "!" ? 'NOT' : ''
375 nl = operator == "!" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
375 nl = operator == "!" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
376 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}, #{MemberRole.table_name}" +
376 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}, #{MemberRole.table_name}" +
377 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id AND #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}))"
377 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id AND #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}))"
378 end
378 end
379 end
379 end
380
380
381 def sql_for_is_private_field(field, operator, value)
381 def sql_for_is_private_field(field, operator, value)
382 op = (operator == "=" ? 'IN' : 'NOT IN')
382 op = (operator == "=" ? 'IN' : 'NOT IN')
383 va = value.map {|v| v == '0' ? connection.quoted_false : connection.quoted_true}.uniq.join(',')
383 va = value.map {|v| v == '0' ? connection.quoted_false : connection.quoted_true}.uniq.join(',')
384
384
385 "#{Issue.table_name}.is_private #{op} (#{va})"
385 "#{Issue.table_name}.is_private #{op} (#{va})"
386 end
386 end
387
387
388 def sql_for_relations(field, operator, value, options={})
388 def sql_for_relations(field, operator, value, options={})
389 relation_options = IssueRelation::TYPES[field]
389 relation_options = IssueRelation::TYPES[field]
390 return relation_options unless relation_options
390 return relation_options unless relation_options
391
391
392 relation_type = field
392 relation_type = field
393 join_column, target_join_column = "issue_from_id", "issue_to_id"
393 join_column, target_join_column = "issue_from_id", "issue_to_id"
394 if relation_options[:reverse] || options[:reverse]
394 if relation_options[:reverse] || options[:reverse]
395 relation_type = relation_options[:reverse] || relation_type
395 relation_type = relation_options[:reverse] || relation_type
396 join_column, target_join_column = target_join_column, join_column
396 join_column, target_join_column = target_join_column, join_column
397 end
397 end
398
398
399 sql = case operator
399 sql = case operator
400 when "*", "!*"
400 when "*", "!*"
401 op = (operator == "*" ? 'IN' : 'NOT IN')
401 op = (operator == "*" ? 'IN' : 'NOT IN')
402 "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name} WHERE #{IssueRelation.table_name}.relation_type = '#{connection.quote_string(relation_type)}')"
402 "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name} WHERE #{IssueRelation.table_name}.relation_type = '#{connection.quote_string(relation_type)}')"
403 when "=", "!"
403 when "=", "!"
404 op = (operator == "=" ? 'IN' : 'NOT IN')
404 op = (operator == "=" ? 'IN' : 'NOT IN')
405 "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name} WHERE #{IssueRelation.table_name}.relation_type = '#{connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = #{value.first.to_i})"
405 "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name} WHERE #{IssueRelation.table_name}.relation_type = '#{connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = #{value.first.to_i})"
406 when "=p", "=!p", "!p"
406 when "=p", "=!p", "!p"
407 op = (operator == "!p" ? 'NOT IN' : 'IN')
407 op = (operator == "!p" ? 'NOT IN' : 'IN')
408 comp = (operator == "=!p" ? '<>' : '=')
408 comp = (operator == "=!p" ? '<>' : '=')
409 "#{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 = '#{connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = relissues.id AND relissues.project_id #{comp} #{value.first.to_i})"
409 "#{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 = '#{connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = relissues.id AND relissues.project_id #{comp} #{value.first.to_i})"
410 end
410 end
411
411
412 if relation_options[:sym] == field && !options[:reverse]
412 if relation_options[:sym] == field && !options[:reverse]
413 sqls = [sql, sql_for_relations(field, operator, value, :reverse => true)]
413 sqls = [sql, sql_for_relations(field, operator, value, :reverse => true)]
414 sqls.join(["!", "!*", "!p"].include?(operator) ? " AND " : " OR ")
414 sqls.join(["!", "!*", "!p"].include?(operator) ? " AND " : " OR ")
415 else
415 else
416 sql
416 sql
417 end
417 end
418 end
418 end
419
419
420 IssueRelation::TYPES.keys.each do |relation_type|
420 IssueRelation::TYPES.keys.each do |relation_type|
421 alias_method "sql_for_#{relation_type}_field".to_sym, :sql_for_relations
421 alias_method "sql_for_#{relation_type}_field".to_sym, :sql_for_relations
422 end
422 end
423 end
423 end
General Comments 0
You need to be logged in to leave comments. Login now