Ability to group and sort the issue list by user/version custom field (#9419).

git-svn-id: svn+ssh://rubyforge.org/var/svn/redmine/trunk@10073 e93f8b46-1217-0410-a6f0-8f06a7374b81

2 files changed, 66 insertions, 6 deletions

diff --git a/app/models/custom_field.rb b/app/models/custom_field.rb
index 408b54afb..3373fb8d8 100644
--- a/app/models/custom_field.rb
+++ b/app/models/custom_field.rb
@@ -146,6 +146,8 @@ class CustomField < ActiveRecord::Base
           " WHERE cv_sort.customized_type='#{self.class.customized_class.base_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)"
+      when 'user', 'version'
+        value_class.fields_for_order_statement(value_join_alias)
       else
         nil
     end
@@ -158,15 +160,57 @@ class CustomField < ActiveRecord::Base
     case field_format
       when 'list', 'date', 'bool', 'int'
         order_statement
+      when 'user', 'version'
+        "COALESCE((SELECT cv_sort.value FROM #{CustomValue.table_name} cv_sort" +
+          " WHERE cv_sort.customized_type='#{self.class.customized_class.base_class.name}'" +
+          " AND cv_sort.customized_id=#{self.class.customized_class.table_name}.id" +
+          " AND cv_sort.custom_field_id=#{id} LIMIT 1), '')"
+      else
+        nil
+    end
+  end
+
+  def join_for_order_statement
+    case field_format
+      when 'user', 'version'
+        "LEFT OUTER JOIN #{CustomValue.table_name} #{join_alias}" +
+          " ON #{join_alias}.customized_type = '#{self.class.customized_class.base_class.name}'" +
+          " AND #{join_alias}.customized_id = #{self.class.customized_class.table_name}.id" +
+          " AND #{join_alias}.custom_field_id = #{id}" +
+          " AND #{join_alias}.value <> ''" +
+          " AND #{join_alias}.id = (SELECT max(#{join_alias}_2.id) FROM #{CustomValue.table_name} #{join_alias}_2" +
+            " WHERE #{join_alias}_2.customized_type = #{join_alias}.customized_type" +
+            " AND #{join_alias}_2.customized_id = #{join_alias}.customized_id" +
+            " AND #{join_alias}_2.custom_field_id = #{join_alias}.custom_field_id)" +
+          " LEFT OUTER JOIN #{value_class.table_name} #{value_join_alias}" +
+          " ON CAST(#{join_alias}.value as decimal(60,0)) = #{value_join_alias}.id"
       else
         nil
     end
   end
 
+  def join_alias
+    "cf_#{id}"
+  end
+
+  def value_join_alias
+    join_alias + "_" + field_format
+  end
+
   def <=>(field)
     position <=> field.position
   end
 
+  # Returns the class that values represent
+  def value_class
+    case field_format
+      when 'user', 'version'
+        field_format.classify.constantize
+      else
+        nil
+    end
+  end
+
   def self.customized_class
     self.name =~ /^(.+)CustomField$/
     begin; $1.constantize; rescue nil; end
diff --git a/app/models/query.rb b/app/models/query.rb
index 5e76b17fe..2d9ab303c 100644
--- a/app/models/query.rb
+++ b/app/models/query.rb
@@ -604,13 +604,11 @@ class Query < ActiveRecord::Base
   def issues(options={})
     order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
     order_option = nil if order_option.blank?
-    
-    joins = (order_option && order_option.include?('authors')) ? "LEFT OUTER JOIN users authors ON authors.id = #{Issue.table_name}.author_id" : nil
 
     issues = Issue.visible.scoped(:conditions => options[:conditions]).find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
                      :conditions => statement,
                      :order => order_option,
-                     :joins => joins,
+                     :joins => joins_for_order_statement(order_option),
                      :limit  => options[:limit],
                      :offset => options[:offset]
 
@@ -626,13 +624,11 @@ class Query < ActiveRecord::Base
   def issue_ids(options={})
     order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
     order_option = nil if order_option.blank?
-    
-    joins = (order_option && order_option.include?('authors')) ? "LEFT OUTER JOIN users authors ON authors.id = #{Issue.table_name}.author_id" : nil
 
     Issue.visible.scoped(:conditions => options[:conditions]).scoped(:include => ([:status, :project] + (options[:include] || [])).uniq,
                      :conditions => statement,
                      :order => order_option,
-                     :joins => joins,
+                     :joins => joins_for_order_statement(order_option),
                      :limit  => options[:limit],
                      :offset => options[:offset]).find_ids
   rescue ::ActiveRecord::StatementInvalid => e
@@ -895,4 +891,24 @@ class Query < ActiveRecord::Base
   def relative_date_clause(table, field, days_from, days_to)
     date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
   end
+
+  # Additional joins required for the given sort options
+  def joins_for_order_statement(order_options)
+    joins = []
+
+    if order_options
+      if order_options.include?('authors')
+        joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{Issue.table_name}.author_id"
+      end
+      order_options.scan(/cf_\d+/).uniq.each do |name|
+        column = available_columns.detect {|c| c.name.to_s == name}
+        join = column.custom_field.join_for_order_statement
+        if join
+          joins << join
+        end
+      end
+    end
+
+    joins.any? ? joins.join(' ') : nil
+  end
 end