jro_apps/redmine Commit - r2256:da01ee7c37f2

Ability to sort the issue list by text, int and float custom fields (#1139)....

Jean-Philippe Lang -

r2256:da01ee7c37f2

parent child

Context file:

r2256:da01ee7c37f2

Collapse all files

app/models/custom_field.rb +9 -1

             # redMine - project management software
             # Copyright (C) 2006  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 CustomField < ActiveRecord::Base
               has_many :custom_values, :dependent => :delete_all
               acts_as_list :scope => 'type = \'#{self.class}\''
               serialize :possible_values
               FIELD_FORMATS = { "string" => { :name => :label_string, :order => 1 },
                                 "text" => { :name => :label_text, :order => 2 },
                                 "int" => { :name => :label_integer, :order => 3 },
                                 "float" => { :name => :label_float, :order => 4 },
                                 "list" => { :name => :label_list, :order => 5 },
             			        "date" => { :name => :label_date, :order => 6 },
             			        "bool" => { :name => :label_boolean, :order => 7 }
               }.freeze
               validates_presence_of :name, :field_format
               validates_uniqueness_of :name, :scope => :type
               validates_length_of :name, :maximum => 30
               validates_format_of :name, :with => /^[\w\s\.\'\-]*$/i
               validates_inclusion_of :field_format, :in => FIELD_FORMATS.keys
               def initialize(attributes = nil)
                 super
                 self.possible_values ||= []
               end
               def before_validation
                 # remove empty values
                 self.possible_values = self.possible_values.collect{|v| v unless v.empty?}.compact
                 # make sure these fields are not searchable
                 self.searchable = false if %w(int float date bool).include?(field_format)
                 true
               end
               def validate
                 if self.field_format == "list"
                   errors.add(:possible_values, :activerecord_error_blank) if self.possible_values.nil? || self.possible_values.empty?
                   errors.add(:possible_values, :activerecord_error_invalid) unless self.possible_values.is_a? Array
                 end
                 # validate default value
                 v = CustomValue.new(:custom_field => self.clone, :value => default_value, :customized => nil)
                 v.custom_field.is_required = false
                 errors.add(:default_value, :activerecord_error_invalid) unless v.valid?
               end
               # Returns a ORDER BY clause that can used to sort customized
               # objects by their value of the custom field.
               # Returns false, if the custom field can not be used for sorting.
               def order_statement
                 case field_format
-                  when 'string', 'list', 'date', 'bool'
+                  when 'string', 'text', 'list', 'date', 'bool'
                     # COALESCE is here to make sure that blank and NULL values are sorted equally
                     "COALESCE((SELECT cv_sort.value FROM #{CustomValue.table_name} cv_sort" +
                       " WHERE cv_sort.customized_type='#{self.class.customized_class.name}'" +
                       " AND cv_sort.customized_id=#{self.class.customized_class.table_name}.id" +
                       " AND cv_sort.custom_field_id=#{id} LIMIT 1), '')"
+                  when 'int', 'float'
+                    # Make the database cast values into numeric
+                    # Postgresql will raise an error if a value can not be casted!
+                    # CustomValue validations should ensure that it doesn't occur
+                    "(SELECT CAST(cv_sort.value AS decimal(60,3)) FROM #{CustomValue.table_name} cv_sort" +
+                      " WHERE cv_sort.customized_type='#{self.class.customized_class.name}'" +
+                      " AND cv_sort.customized_id=#{self.class.customized_class.table_name}.id" +
+                      " AND cv_sort.custom_field_id=#{id} AND cv_sort.value <> '' AND cv_sort.value IS NOT NULL LIMIT 1)"
                   else
                     nil
                 end
               end
               def <=>(field)
                 position <=> field.position
               end
               def self.customized_class
                 self.name =~ /^(.+)CustomField$/
                 begin; $1.constantize; rescue nil; end
               end
               # to move in project_custom_field
               def self.for_all
                 find(:all, :conditions => ["is_for_all=?", true], :order => 'position')
               end
               def type_name
                 nil
               end
             end

test/fixtures/custom_fields.yml +12 0

             ---
             custom_fields_001:
               name: Database
               min_length: 0
               regexp: ""
               is_for_all: true
               is_filter: true
               type: IssueCustomField
               max_length: 0
               possible_values:
               - MySQL
               - PostgreSQL
               - Oracle
               id: 1
               is_required: false
               field_format: list
               default_value: ""
             custom_fields_002:
               name: Searchable field
               min_length: 1
               regexp: ""
               is_for_all: true
               type: IssueCustomField
               max_length: 100
               possible_values: ""
               id: 2
               is_required: false
               field_format: string
               searchable: true
               default_value: "Default string"
             custom_fields_003:
               name: Development status
               min_length: 0
               regexp: ""
               is_for_all: false
               is_filter: true
               type: ProjectCustomField
               max_length: 0
               possible_values:
               - Stable
               - Beta
               - Alpha
               - Planning
               id: 3
               is_required: true
               field_format: list
               default_value: ""
             custom_fields_004:
               name: Phone number
               min_length: 0
               regexp: ""
               is_for_all: false
               type: UserCustomField
               max_length: 0
               possible_values: ""
               id: 4
               is_required: false
               field_format: string
               default_value: ""
             custom_fields_005:
               name: Money
               min_length: 0
               regexp: ""
               is_for_all: false
               type: UserCustomField
               max_length: 0
               possible_values: ""
               id: 5
               is_required: false
               field_format: float
               default_value: ""
+            custom_fields_006:
+              name: Float field
+              min_length: 0
+              regexp: ""
+              is_for_all: true
+              type: IssueCustomField
+              max_length: 0
+              possible_values: ""
+              id: 6
+              is_required: false
+              field_format: float
+              default_value: ""
   No newline at end of file

test/fixtures/custom_fields_trackers.yml +9 0

             ---
             custom_fields_trackers_001:
               custom_field_id: 1
               tracker_id: 1
             custom_fields_trackers_002:
               custom_field_id: 2
               tracker_id: 1
             custom_fields_trackers_003:
               custom_field_id: 2
               tracker_id: 3
+            custom_fields_trackers_004:
+              custom_field_id: 6
+              tracker_id: 1
+            custom_fields_trackers_005:
+              custom_field_id: 6
+              tracker_id: 2
+            custom_fields_trackers_006:
+              custom_field_id: 6
+              tracker_id: 3

test/fixtures/custom_values.yml +39 -9

             ---
             custom_values_006:
               customized_type: Issue
               custom_field_id: 2
               customized_id: 3
-              id: 9
+              id: 6
               value: "125"
             custom_values_007:
               customized_type: Project
               custom_field_id: 3
               customized_id: 1
-              id: 10
+              id: 7
               value: Stable
             custom_values_001:
               customized_type: User
               custom_field_id: 4
               customized_id: 3
-              id: 2
+              id: 1
               value: ""
             custom_values_002:
               customized_type: User
               custom_field_id: 4
               customized_id: 4
-              id: 3
+              id: 2
               value: 01 23 45 67 89
             custom_values_003:
               customized_type: User
               custom_field_id: 4
               customized_id: 2
-              id: 4
+              id: 3
               value: ""
             custom_values_004:
               customized_type: Issue
               custom_field_id: 2
               customized_id: 1
-              id: 7
+              id: 4
               value: "125"
             custom_values_005:
               customized_type: Issue
               custom_field_id: 2
               customized_id: 2
-              id: 8
+              id: 5
               value: ""
             custom_values_008:
               customized_type: Issue
               custom_field_id: 1
               customized_id: 3
-              id: 11
+              id: 8
               value: "MySQL"
             custom_values_009:
               customized_type: Issue
               custom_field_id: 2
               customized_id: 3
-              id: 12
+              id: 9
               value: "this is a stringforcustomfield search"
+            custom_values_010:
+              customized_type: Issue
+              custom_field_id: 6
+              customized_id: 1
+              id: 10
+              value: "2.1"
+            custom_values_011:
+              customized_type: Issue
+              custom_field_id: 6
+              customized_id: 2
+              id: 11
+              value: "2.05"
+            custom_values_012:
+              customized_type: Issue
+              custom_field_id: 6
+              customized_id: 3
+              id: 12
+              value: "11.65"
+            custom_values_013:
+              customized_type: Issue
+              custom_field_id: 6
+              customized_id: 7
+              id: 13
+              value: ""
+            custom_values_014:
+              customized_type: Issue
+              custom_field_id: 6
+              customized_id: 5
+              id: 14
+              value: "-7.6"
   No newline at end of file

test/unit/query_test.rb +16 0

             # redMine - project management software
             # Copyright (C) 2006-2008  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.
             require File.dirname(__FILE__) + '/../test_helper'
             class QueryTest < Test::Unit::TestCase
               fixtures :projects, :enabled_modules, :users, :members, :roles, :trackers, :issue_statuses, :issue_categories, :enumerations, :issues, :custom_fields, :custom_values, :versions, :queries
               def test_custom_fields_for_all_projects_should_be_available_in_global_queries
                 query = Query.new(:project => nil, :name => '_')
                 assert query.available_filters.has_key?('cf_1')
                 assert !query.available_filters.has_key?('cf_3')
               end
               def find_issues_with_query(query)
                 Issue.find :all,
                   :include => [ :assigned_to, :status, :tracker, :project, :priority ],
                   :conditions => query.statement
               end
               def test_query_with_multiple_custom_fields
                 query = Query.find(1)
                 assert query.valid?
                 assert query.statement.include?("#{CustomValue.table_name}.value IN ('MySQL')")
                 issues = find_issues_with_query(query)
                 assert_equal 1, issues.length
                 assert_equal Issue.find(3), issues.first
               end
               def test_operator_none
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('fixed_version_id', '!*', [''])
                 query.add_filter('cf_1', '!*', [''])
                 assert query.statement.include?("#{Issue.table_name}.fixed_version_id IS NULL")
                 assert query.statement.include?("#{CustomValue.table_name}.value IS NULL OR #{CustomValue.table_name}.value = ''")
                 find_issues_with_query(query)
               end
               def test_operator_none_for_integer
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('estimated_hours', '!*', [''])
                 issues = find_issues_with_query(query)
                 assert !issues.empty?
                 assert issues.all? {|i| !i.estimated_hours}
               end
               def test_operator_all
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('fixed_version_id', '*', [''])
                 query.add_filter('cf_1', '*', [''])
                 assert query.statement.include?("#{Issue.table_name}.fixed_version_id IS NOT NULL")
                 assert query.statement.include?("#{CustomValue.table_name}.value IS NOT NULL AND #{CustomValue.table_name}.value <> ''")
                 find_issues_with_query(query)
               end
               def test_operator_greater_than
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('done_ratio', '>=', ['40'])
                 assert query.statement.include?("#{Issue.table_name}.done_ratio >= 40")
                 find_issues_with_query(query)
               end
               def test_operator_in_more_than
                 Issue.find(7).update_attribute(:due_date, (Date.today + 15))
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('due_date', '>t+', ['15'])
                 issues = find_issues_with_query(query)
                 assert !issues.empty?
                 issues.each {|issue| assert(issue.due_date >= (Date.today + 15))}
               end
               def test_operator_in_less_than
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('due_date', '<t+', ['15'])
                 issues = find_issues_with_query(query)
                 assert !issues.empty?
                 issues.each {|issue| assert(issue.due_date >= Date.today && issue.due_date <= (Date.today + 15))}
               end
               def test_operator_less_than_ago
                 Issue.find(7).update_attribute(:due_date, (Date.today - 3))
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('due_date', '>t-', ['3'])
                 issues = find_issues_with_query(query)
                 assert !issues.empty?
                 issues.each {|issue| assert(issue.due_date >= (Date.today - 3) && issue.due_date <= Date.today)}
               end
               def test_operator_more_than_ago
                 Issue.find(7).update_attribute(:due_date, (Date.today - 10))
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('due_date', '<t-', ['10'])
                 assert query.statement.include?("#{Issue.table_name}.due_date <=")
                 issues = find_issues_with_query(query)
                 assert !issues.empty?
                 issues.each {|issue| assert(issue.due_date <= (Date.today - 10))}
               end
               def test_operator_in
                 Issue.find(7).update_attribute(:due_date, (Date.today + 2))
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('due_date', 't+', ['2'])
                 issues = find_issues_with_query(query)
                 assert !issues.empty?
                 issues.each {|issue| assert_equal((Date.today + 2), issue.due_date)}
               end
               def test_operator_ago
                 Issue.find(7).update_attribute(:due_date, (Date.today - 3))
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('due_date', 't-', ['3'])
                 issues = find_issues_with_query(query)
                 assert !issues.empty?
                 issues.each {|issue| assert_equal((Date.today - 3), issue.due_date)}
               end
               def test_operator_today
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('due_date', 't', [''])
                 issues = find_issues_with_query(query)
                 assert !issues.empty?
                 issues.each {|issue| assert_equal Date.today, issue.due_date}
               end
               def test_operator_this_week_on_date
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('due_date', 'w', [''])
                 find_issues_with_query(query)
               end
               def test_operator_this_week_on_datetime
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('created_on', 'w', [''])
                 find_issues_with_query(query)
               end
               def test_operator_contains
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('subject', '~', ['string'])
                 assert query.statement.include?("#{Issue.table_name}.subject LIKE '%string%'")
                 find_issues_with_query(query)
               end
               def test_operator_does_not_contains
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('subject', '!~', ['string'])
                 assert query.statement.include?("#{Issue.table_name}.subject NOT LIKE '%string%'")
                 find_issues_with_query(query)
               end
               def test_default_columns
                 q = Query.new
                 assert !q.columns.empty?
               end
               def test_set_column_names
                 q = Query.new
                 q.column_names = ['tracker', :subject, '', 'unknonw_column']
                 assert_equal [:tracker, :subject], q.columns.collect {|c| c.name}
                 c = q.columns.first
                 assert q.has_column?(c)
               end
               def test_sort_by_string_custom_field_asc
                 q = Query.new
                 c = q.available_columns.find {|col| col.is_a?(QueryCustomFieldColumn) && col.custom_field.field_format == 'string' }
                 assert c
                 assert c.sortable
                 issues = Issue.find :all,
                                     :include => [ :assigned_to, :status, :tracker, :project, :priority ],
                                     :conditions => q.statement,
                                     :order => "#{c.sortable} ASC"
                 values = issues.collect {|i| i.custom_value_for(c.custom_field).to_s}
+                assert !values.empty?
                 assert_equal values.sort, values
               end
               def test_sort_by_string_custom_field_desc
                 q = Query.new
                 c = q.available_columns.find {|col| col.is_a?(QueryCustomFieldColumn) && col.custom_field.field_format == 'string' }
                 assert c
                 assert c.sortable
                 issues = Issue.find :all,
                                     :include => [ :assigned_to, :status, :tracker, :project, :priority ],
                                     :conditions => q.statement,
                                     :order => "#{c.sortable} DESC"
                 values = issues.collect {|i| i.custom_value_for(c.custom_field).to_s}
+                assert !values.empty?
                 assert_equal values.sort.reverse, values
               end
+              def test_sort_by_float_custom_field_asc
+                q = Query.new
+                c = q.available_columns.find {|col| col.is_a?(QueryCustomFieldColumn) && col.custom_field.field_format == 'float' }
+                assert c
+                assert c.sortable
+                issues = Issue.find :all,
+                                    :include => [ :assigned_to, :status, :tracker, :project, :priority ],
+                                    :conditions => q.statement,
+                                    :order => "#{c.sortable} ASC"
+                values = issues.collect {|i| begin; Kernel.Float(i.custom_value_for(c.custom_field).to_s); rescue; nil; end}.compact
+                assert !values.empty?
+                assert_equal values.sort, values
+              end
               def test_label_for
                 q = Query.new
                 assert_equal 'assigned_to', q.label_for('assigned_to_id')
               end
               def test_editable_by
                 admin = User.find(1)
                 manager = User.find(2)
                 developer = User.find(3)
                 # Public query on project 1
                 q = Query.find(1)
                 assert q.editable_by?(admin)
                 assert q.editable_by?(manager)
                 assert !q.editable_by?(developer)
                 # Private query on project 1
                 q = Query.find(2)
                 assert q.editable_by?(admin)
                 assert !q.editable_by?(manager)
                 assert q.editable_by?(developer)
                 # Private query for all projects
                 q = Query.find(3)
                 assert q.editable_by?(admin)
                 assert !q.editable_by?(manager)
                 assert q.editable_by?(developer)
                 # Public query for all projects
                 q = Query.find(4)
                 assert q.editable_by?(admin)
                 assert !q.editable_by?(manager)
                 assert !q.editable_by?(developer)
               end
             end

General Comments 0

Write
Preview

You need to be logged in to leave comments. Login now

No TODOs yet

	Site-wide shortcuts
/	Use quick search box
g h	Goto home page
g g	Goto my private gists page
g G	Goto my public gists page
g 0-9	Goto bookmarked items from 0-9
n r	New repository page
n g	New gist page

	Repositories
g s	Goto summary page
g c	Goto changelog page
g f	Goto files page
g F	Goto files page with file search activated
g p	Goto pull requests page
g o	Goto repository settings
g O	Goto repository access permissions settings
t s	Toggle sidebar on some pages