##// END OF EJS Templates
jro_apps » redmine
RSS
Ability to sort the issue list by text, int and float custom fields (#1139)....
Jean-Philippe Lang -
r2256:da01ee7c37f2
parent child
Show More
Side by Side Unified
Context file:
r2256:da01ee7c37f2 Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,96 +1,104
1 1 # redMine - project management software
2 2 # Copyright (C) 2006 Jean-Philippe Lang
3 3 #
4 4 # This program is free software; you can redistribute it and/or
5 5 # modify it under the terms of the GNU General Public License
6 6 # as published by the Free Software Foundation; either version 2
7 7 # of the License, or (at your option) any later version.
8 8 #
9 9 # This program is distributed in the hope that it will be useful,
10 10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 12 # GNU General Public License for more details.
13 13 #
14 14 # You should have received a copy of the GNU General Public License
15 15 # along with this program; if not, write to the Free Software
16 16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 17
18 18 class CustomField < ActiveRecord::Base
19 19 has_many :custom_values, :dependent => :delete_all
20 20 acts_as_list :scope => 'type = \'#{self.class}\''
21 21 serialize :possible_values
22 22
23 23 FIELD_FORMATS = { "string" => { :name => :label_string, :order => 1 },
24 24 "text" => { :name => :label_text, :order => 2 },
25 25 "int" => { :name => :label_integer, :order => 3 },
26 26 "float" => { :name => :label_float, :order => 4 },
27 27 "list" => { :name => :label_list, :order => 5 },
28 28 "date" => { :name => :label_date, :order => 6 },
29 29 "bool" => { :name => :label_boolean, :order => 7 }
30 30 }.freeze
31 31
32 32 validates_presence_of :name, :field_format
33 33 validates_uniqueness_of :name, :scope => :type
34 34 validates_length_of :name, :maximum => 30
35 35 validates_format_of :name, :with => /^[\w\s\.\'\-]*$/i
36 36 validates_inclusion_of :field_format, :in => FIELD_FORMATS.keys
37 37
38 38 def initialize(attributes = nil)
39 39 super
40 40 self.possible_values ||= []
41 41 end
42 42
43 43 def before_validation
44 44 # remove empty values
45 45 self.possible_values = self.possible_values.collect{|v| v unless v.empty?}.compact
46 46 # make sure these fields are not searchable
47 47 self.searchable = false if %w(int float date bool).include?(field_format)
48 48 true
49 49 end
50 50
51 51 def validate
52 52 if self.field_format == "list"
53 53 errors.add(:possible_values, :activerecord_error_blank) if self.possible_values.nil? || self.possible_values.empty?
54 54 errors.add(:possible_values, :activerecord_error_invalid) unless self.possible_values.is_a? Array
55 55 end
56 56
57 57 # validate default value
58 58 v = CustomValue.new(:custom_field => self.clone, :value => default_value, :customized => nil)
59 59 v.custom_field.is_required = false
60 60 errors.add(:default_value, :activerecord_error_invalid) unless v.valid?
61 61 end
62 62
63 63 # Returns a ORDER BY clause that can used to sort customized
64 64 # objects by their value of the custom field.
65 65 # Returns false, if the custom field can not be used for sorting.
66 66 def order_statement
67 67 case field_format
68 when 'string', 'list', 'date', 'bool'
68 when 'string', 'text', 'list', 'date', 'bool'
69 69 # COALESCE is here to make sure that blank and NULL values are sorted equally
70 70 "COALESCE((SELECT cv_sort.value FROM #{CustomValue.table_name} cv_sort" +
71 71 " WHERE cv_sort.customized_type='#{self.class.customized_class.name}'" +
72 72 " AND cv_sort.customized_id=#{self.class.customized_class.table_name}.id" +
73 73 " AND cv_sort.custom_field_id=#{id} LIMIT 1), '')"
74 when 'int', 'float'
75 # Make the database cast values into numeric
76 # Postgresql will raise an error if a value can not be casted!
77 # CustomValue validations should ensure that it doesn't occur
78 "(SELECT CAST(cv_sort.value AS decimal(60,3)) FROM #{CustomValue.table_name} cv_sort" +
79 " WHERE cv_sort.customized_type='#{self.class.customized_class.name}'" +
80 " AND cv_sort.customized_id=#{self.class.customized_class.table_name}.id" +
81 " AND cv_sort.custom_field_id=#{id} AND cv_sort.value <> '' AND cv_sort.value IS NOT NULL LIMIT 1)"
74 82 else
75 83 nil
76 84 end
77 85 end
78 86
79 87 def <=>(field)
80 88 position <=> field.position
81 89 end
82 90
83 91 def self.customized_class
84 92 self.name =~ /^(.+)CustomField$/
85 93 begin; $1.constantize; rescue nil; end
86 94 end
87 95
88 96 # to move in project_custom_field
89 97 def self.for_all
90 98 find(:all, :conditions => ["is_for_all=?", true], :order => 'position')
91 99 end
92 100
93 101 def type_name
94 102 nil
95 103 end
96 104 end
Show file before | Show file after | Hide comments
@@ -1,72 +1,84
1 1 ---
2 2 custom_fields_001:
3 3 name: Database
4 4 min_length: 0
5 5 regexp: ""
6 6 is_for_all: true
7 7 is_filter: true
8 8 type: IssueCustomField
9 9 max_length: 0
10 10 possible_values:
11 11 - MySQL
12 12 - PostgreSQL
13 13 - Oracle
14 14 id: 1
15 15 is_required: false
16 16 field_format: list
17 17 default_value: ""
18 18 custom_fields_002:
19 19 name: Searchable field
20 20 min_length: 1
21 21 regexp: ""
22 22 is_for_all: true
23 23 type: IssueCustomField
24 24 max_length: 100
25 25 possible_values: ""
26 26 id: 2
27 27 is_required: false
28 28 field_format: string
29 29 searchable: true
30 30 default_value: "Default string"
31 31 custom_fields_003:
32 32 name: Development status
33 33 min_length: 0
34 34 regexp: ""
35 35 is_for_all: false
36 36 is_filter: true
37 37 type: ProjectCustomField
38 38 max_length: 0
39 39 possible_values:
40 40 - Stable
41 41 - Beta
42 42 - Alpha
43 43 - Planning
44 44 id: 3
45 45 is_required: true
46 46 field_format: list
47 47 default_value: ""
48 48 custom_fields_004:
49 49 name: Phone number
50 50 min_length: 0
51 51 regexp: ""
52 52 is_for_all: false
53 53 type: UserCustomField
54 54 max_length: 0
55 55 possible_values: ""
56 56 id: 4
57 57 is_required: false
58 58 field_format: string
59 59 default_value: ""
60 60 custom_fields_005:
61 61 name: Money
62 62 min_length: 0
63 63 regexp: ""
64 64 is_for_all: false
65 65 type: UserCustomField
66 66 max_length: 0
67 67 possible_values: ""
68 68 id: 5
69 69 is_required: false
70 70 field_format: float
71 71 default_value: ""
72 custom_fields_006:
73 name: Float field
74 min_length: 0
75 regexp: ""
76 is_for_all: true
77 type: IssueCustomField
78 max_length: 0
79 possible_values: ""
80 id: 6
81 is_required: false
82 field_format: float
83 default_value: ""
72 84 No newline at end of file
Show file before | Show file after | Hide comments
@@ -1,10 +1,19
1 1 ---
2 2 custom_fields_trackers_001:
3 3 custom_field_id: 1
4 4 tracker_id: 1
5 5 custom_fields_trackers_002:
6 6 custom_field_id: 2
7 7 tracker_id: 1
8 8 custom_fields_trackers_003:
9 9 custom_field_id: 2
10 10 tracker_id: 3
11 custom_fields_trackers_004:
12 custom_field_id: 6
13 tracker_id: 1
14 custom_fields_trackers_005:
15 custom_field_id: 6
16 tracker_id: 2
17 custom_fields_trackers_006:
18 custom_field_id: 6
19 tracker_id: 3
Show file before | Show file after | Hide comments
@@ -1,56 +1,86
1 1 ---
2 2 custom_values_006:
3 3 customized_type: Issue
4 4 custom_field_id: 2
5 5 customized_id: 3
6 id: 9
6 id: 6
7 7 value: "125"
8 8 custom_values_007:
9 9 customized_type: Project
10 10 custom_field_id: 3
11 11 customized_id: 1
12 id: 10
12 id: 7
13 13 value: Stable
14 14 custom_values_001:
15 15 customized_type: User
16 16 custom_field_id: 4
17 17 customized_id: 3
18 id: 2
18 id: 1
19 19 value: ""
20 20 custom_values_002:
21 21 customized_type: User
22 22 custom_field_id: 4
23 23 customized_id: 4
24 id: 3
24 id: 2
25 25 value: 01 23 45 67 89
26 26 custom_values_003:
27 27 customized_type: User
28 28 custom_field_id: 4
29 29 customized_id: 2
30 id: 4
30 id: 3
31 31 value: ""
32 32 custom_values_004:
33 33 customized_type: Issue
34 34 custom_field_id: 2
35 35 customized_id: 1
36 id: 7
36 id: 4
37 37 value: "125"
38 38 custom_values_005:
39 39 customized_type: Issue
40 40 custom_field_id: 2
41 41 customized_id: 2
42 id: 8
42 id: 5
43 43 value: ""
44 44 custom_values_008:
45 45 customized_type: Issue
46 46 custom_field_id: 1
47 47 customized_id: 3
48 id: 11
48 id: 8
49 49 value: "MySQL"
50 50 custom_values_009:
51 51 customized_type: Issue
52 52 custom_field_id: 2
53 53 customized_id: 3
54 id: 12
54 id: 9
55 55 value: "this is a stringforcustomfield search"
56 custom_values_010:
57 customized_type: Issue
58 custom_field_id: 6
59 customized_id: 1
60 id: 10
61 value: "2.1"
62 custom_values_011:
63 customized_type: Issue
64 custom_field_id: 6
65 customized_id: 2
66 id: 11
67 value: "2.05"
68 custom_values_012:
69 customized_type: Issue
70 custom_field_id: 6
71 customized_id: 3
72 id: 12
73 value: "11.65"
74 custom_values_013:
75 customized_type: Issue
76 custom_field_id: 6
77 customized_id: 7
78 id: 13
79 value: ""
80 custom_values_014:
81 customized_type: Issue
82 custom_field_id: 6
83 customized_id: 5
84 id: 14
85 value: "-7.6"
56 86 No newline at end of file
Show file before | Show file after | Hide comments
@@ -1,238 +1,254
1 1 # redMine - project management software
2 2 # Copyright (C) 2006-2008 Jean-Philippe Lang
3 3 #
4 4 # This program is free software; you can redistribute it and/or
5 5 # modify it under the terms of the GNU General Public License
6 6 # as published by the Free Software Foundation; either version 2
7 7 # of the License, or (at your option) any later version.
8 8 #
9 9 # This program is distributed in the hope that it will be useful,
10 10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 12 # GNU General Public License for more details.
13 13 #
14 14 # You should have received a copy of the GNU General Public License
15 15 # along with this program; if not, write to the Free Software
16 16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 17
18 18 require File.dirname(__FILE__) + '/../test_helper'
19 19
20 20 class QueryTest < Test::Unit::TestCase
21 21 fixtures :projects, :enabled_modules, :users, :members, :roles, :trackers, :issue_statuses, :issue_categories, :enumerations, :issues, :custom_fields, :custom_values, :versions, :queries
22 22
23 23 def test_custom_fields_for_all_projects_should_be_available_in_global_queries
24 24 query = Query.new(:project => nil, :name => '_')
25 25 assert query.available_filters.has_key?('cf_1')
26 26 assert !query.available_filters.has_key?('cf_3')
27 27 end
28 28
29 29 def find_issues_with_query(query)
30 30 Issue.find :all,
31 31 :include => [ :assigned_to, :status, :tracker, :project, :priority ],
32 32 :conditions => query.statement
33 33 end
34 34
35 35 def test_query_with_multiple_custom_fields
36 36 query = Query.find(1)
37 37 assert query.valid?
38 38 assert query.statement.include?("#{CustomValue.table_name}.value IN ('MySQL')")
39 39 issues = find_issues_with_query(query)
40 40 assert_equal 1, issues.length
41 41 assert_equal Issue.find(3), issues.first
42 42 end
43 43
44 44 def test_operator_none
45 45 query = Query.new(:project => Project.find(1), :name => '_')
46 46 query.add_filter('fixed_version_id', '!*', [''])
47 47 query.add_filter('cf_1', '!*', [''])
48 48 assert query.statement.include?("#{Issue.table_name}.fixed_version_id IS NULL")
49 49 assert query.statement.include?("#{CustomValue.table_name}.value IS NULL OR #{CustomValue.table_name}.value = ''")
50 50 find_issues_with_query(query)
51 51 end
52 52
53 53 def test_operator_none_for_integer
54 54 query = Query.new(:project => Project.find(1), :name => '_')
55 55 query.add_filter('estimated_hours', '!*', [''])
56 56 issues = find_issues_with_query(query)
57 57 assert !issues.empty?
58 58 assert issues.all? {|i| !i.estimated_hours}
59 59 end
60 60
61 61 def test_operator_all
62 62 query = Query.new(:project => Project.find(1), :name => '_')
63 63 query.add_filter('fixed_version_id', '*', [''])
64 64 query.add_filter('cf_1', '*', [''])
65 65 assert query.statement.include?("#{Issue.table_name}.fixed_version_id IS NOT NULL")
66 66 assert query.statement.include?("#{CustomValue.table_name}.value IS NOT NULL AND #{CustomValue.table_name}.value <> ''")
67 67 find_issues_with_query(query)
68 68 end
69 69
70 70 def test_operator_greater_than
71 71 query = Query.new(:project => Project.find(1), :name => '_')
72 72 query.add_filter('done_ratio', '>=', ['40'])
73 73 assert query.statement.include?("#{Issue.table_name}.done_ratio >= 40")
74 74 find_issues_with_query(query)
75 75 end
76 76
77 77 def test_operator_in_more_than
78 78 Issue.find(7).update_attribute(:due_date, (Date.today + 15))
79 79 query = Query.new(:project => Project.find(1), :name => '_')
80 80 query.add_filter('due_date', '>t+', ['15'])
81 81 issues = find_issues_with_query(query)
82 82 assert !issues.empty?
83 83 issues.each {|issue| assert(issue.due_date >= (Date.today + 15))}
84 84 end
85 85
86 86 def test_operator_in_less_than
87 87 query = Query.new(:project => Project.find(1), :name => '_')
88 88 query.add_filter('due_date', '<t+', ['15'])
89 89 issues = find_issues_with_query(query)
90 90 assert !issues.empty?
91 91 issues.each {|issue| assert(issue.due_date >= Date.today && issue.due_date <= (Date.today + 15))}
92 92 end
93 93
94 94 def test_operator_less_than_ago
95 95 Issue.find(7).update_attribute(:due_date, (Date.today - 3))
96 96 query = Query.new(:project => Project.find(1), :name => '_')
97 97 query.add_filter('due_date', '>t-', ['3'])
98 98 issues = find_issues_with_query(query)
99 99 assert !issues.empty?
100 100 issues.each {|issue| assert(issue.due_date >= (Date.today - 3) && issue.due_date <= Date.today)}
101 101 end
102 102
103 103 def test_operator_more_than_ago
104 104 Issue.find(7).update_attribute(:due_date, (Date.today - 10))
105 105 query = Query.new(:project => Project.find(1), :name => '_')
106 106 query.add_filter('due_date', '<t-', ['10'])
107 107 assert query.statement.include?("#{Issue.table_name}.due_date <=")
108 108 issues = find_issues_with_query(query)
109 109 assert !issues.empty?
110 110 issues.each {|issue| assert(issue.due_date <= (Date.today - 10))}
111 111 end
112 112
113 113 def test_operator_in
114 114 Issue.find(7).update_attribute(:due_date, (Date.today + 2))
115 115 query = Query.new(:project => Project.find(1), :name => '_')
116 116 query.add_filter('due_date', 't+', ['2'])
117 117 issues = find_issues_with_query(query)
118 118 assert !issues.empty?
119 119 issues.each {|issue| assert_equal((Date.today + 2), issue.due_date)}
120 120 end
121 121
122 122 def test_operator_ago
123 123 Issue.find(7).update_attribute(:due_date, (Date.today - 3))
124 124 query = Query.new(:project => Project.find(1), :name => '_')
125 125 query.add_filter('due_date', 't-', ['3'])
126 126 issues = find_issues_with_query(query)
127 127 assert !issues.empty?
128 128 issues.each {|issue| assert_equal((Date.today - 3), issue.due_date)}
129 129 end
130 130
131 131 def test_operator_today
132 132 query = Query.new(:project => Project.find(1), :name => '_')
133 133 query.add_filter('due_date', 't', [''])
134 134 issues = find_issues_with_query(query)
135 135 assert !issues.empty?
136 136 issues.each {|issue| assert_equal Date.today, issue.due_date}
137 137 end
138 138
139 139 def test_operator_this_week_on_date
140 140 query = Query.new(:project => Project.find(1), :name => '_')
141 141 query.add_filter('due_date', 'w', [''])
142 142 find_issues_with_query(query)
143 143 end
144 144
145 145 def test_operator_this_week_on_datetime
146 146 query = Query.new(:project => Project.find(1), :name => '_')
147 147 query.add_filter('created_on', 'w', [''])
148 148 find_issues_with_query(query)
149 149 end
150 150
151 151 def test_operator_contains
152 152 query = Query.new(:project => Project.find(1), :name => '_')
153 153 query.add_filter('subject', '~', ['string'])
154 154 assert query.statement.include?("#{Issue.table_name}.subject LIKE '%string%'")
155 155 find_issues_with_query(query)
156 156 end
157 157
158 158 def test_operator_does_not_contains
159 159 query = Query.new(:project => Project.find(1), :name => '_')
160 160 query.add_filter('subject', '!~', ['string'])
161 161 assert query.statement.include?("#{Issue.table_name}.subject NOT LIKE '%string%'")
162 162 find_issues_with_query(query)
163 163 end
164 164
165 165 def test_default_columns
166 166 q = Query.new
167 167 assert !q.columns.empty?
168 168 end
169 169
170 170 def test_set_column_names
171 171 q = Query.new
172 172 q.column_names = ['tracker', :subject, '', 'unknonw_column']
173 173 assert_equal [:tracker, :subject], q.columns.collect {|c| c.name}
174 174 c = q.columns.first
175 175 assert q.has_column?(c)
176 176 end
177 177
178 178 def test_sort_by_string_custom_field_asc
179 179 q = Query.new
180 180 c = q.available_columns.find {|col| col.is_a?(QueryCustomFieldColumn) && col.custom_field.field_format == 'string' }
181 181 assert c
182 182 assert c.sortable
183 183 issues = Issue.find :all,
184 184 :include => [ :assigned_to, :status, :tracker, :project, :priority ],
185 185 :conditions => q.statement,
186 186 :order => "#{c.sortable} ASC"
187 187 values = issues.collect {|i| i.custom_value_for(c.custom_field).to_s}
188 assert !values.empty?
188 189 assert_equal values.sort, values
189 190 end
190 191
191 192 def test_sort_by_string_custom_field_desc
192 193 q = Query.new
193 194 c = q.available_columns.find {|col| col.is_a?(QueryCustomFieldColumn) && col.custom_field.field_format == 'string' }
194 195 assert c
195 196 assert c.sortable
196 197 issues = Issue.find :all,
197 198 :include => [ :assigned_to, :status, :tracker, :project, :priority ],
198 199 :conditions => q.statement,
199 200 :order => "#{c.sortable} DESC"
200 201 values = issues.collect {|i| i.custom_value_for(c.custom_field).to_s}
202 assert !values.empty?
201 203 assert_equal values.sort.reverse, values
202 204 end
203 205
206 def test_sort_by_float_custom_field_asc
207 q = Query.new
208 c = q.available_columns.find {|col| col.is_a?(QueryCustomFieldColumn) && col.custom_field.field_format == 'float' }
209 assert c
210 assert c.sortable
211 issues = Issue.find :all,
212 :include => [ :assigned_to, :status, :tracker, :project, :priority ],
213 :conditions => q.statement,
214 :order => "#{c.sortable} ASC"
215 values = issues.collect {|i| begin; Kernel.Float(i.custom_value_for(c.custom_field).to_s); rescue; nil; end}.compact
216 assert !values.empty?
217 assert_equal values.sort, values
218 end
219
204 220 def test_label_for
205 221 q = Query.new
206 222 assert_equal 'assigned_to', q.label_for('assigned_to_id')
207 223 end
208 224
209 225 def test_editable_by
210 226 admin = User.find(1)
211 227 manager = User.find(2)
212 228 developer = User.find(3)
213 229
214 230 # Public query on project 1
215 231 q = Query.find(1)
216 232 assert q.editable_by?(admin)
217 233 assert q.editable_by?(manager)
218 234 assert !q.editable_by?(developer)
219 235
220 236 # Private query on project 1
221 237 q = Query.find(2)
222 238 assert q.editable_by?(admin)
223 239 assert !q.editable_by?(manager)
224 240 assert q.editable_by?(developer)
225 241
226 242 # Private query for all projects
227 243 q = Query.find(3)
228 244 assert q.editable_by?(admin)
229 245 assert !q.editable_by?(manager)
230 246 assert q.editable_by?(developer)
231 247
232 248 # Public query for all projects
233 249 q = Query.find(4)
234 250 assert q.editable_by?(admin)
235 251 assert !q.editable_by?(manager)
236 252 assert !q.editable_by?(developer)
237 253 end
238 254 end
General Comments 0
You need to be logged in to leave comments. Login now