Preload total spent time on the issue list with 1 query (#11253).

git-svn-id: http://svn.redmine.org/redmine/trunk@14407 e93f8b46-1217-0410-a6f0-8f06a7374b81

2 files changed, 16 insertions, 4 deletions

diff --git a/app/models/issue.rb b/app/models/issue.rb
index e7c02ea2e..5c9f81efa 100644
--- a/app/models/issue.rb
+++ b/app/models/issue.rb
@@ -918,11 +918,10 @@ class Issue < ActiveRecord::Base
 
   # Returns the total number of hours spent on this issue and its descendants
   def total_spent_hours
-    if leaf?
+    @total_spent_hours ||= if leaf?
       spent_hours
     else
-      @total_spent_hours ||=
-        self_and_descendants.joins(:time_entries).sum("#{TimeEntry.table_name}.hours").to_f || 0.0
+      self_and_descendants.joins(:time_entries).sum("#{TimeEntry.table_name}.hours").to_f || 0.0
     end
   end
 
@@ -948,7 +947,7 @@ class Issue < ActiveRecord::Base
     end
   end
 
-  # Preloads visible spent time for a collection of issues
+  # Preloads visible total spent time for a collection of issues
   def self.load_visible_spent_hours(issues, user=User.current)
     if issues.any?
       hours_by_issue_id = TimeEntry.visible(user).group(:issue_id).sum(:hours)
@@ -958,6 +957,16 @@ class Issue < ActiveRecord::Base
     end
   end
 
+  def self.load_visible_total_spent_hours(issues, user=User.current)
+    if issues.any?
+      hours_by_issue_id = TimeEntry.visible(user).joins(:issue).joins("JOIN #{Issue.table_name} parent ON parent.root_id = #{Issue.table_name}.root_id" +
+        " AND parent.lft <= #{Issue.table_name}.lft AND parent.rgt >= #{Issue.table_name}.rgt").group("parent.id").sum(:hours)
+      issues.each do |issue|
+        issue.instance_variable_set "@total_spent_hours", (hours_by_issue_id[issue.id] || 0)
+      end
+    end
+  end
+
   # Preloads visible relations for a collection of issues
   def self.load_visible_relations(issues, user=User.current)
     if issues.any?
diff --git a/app/models/issue_query.rb b/app/models/issue_query.rb
index edc1290fe..8316f6b4b 100644
--- a/app/models/issue_query.rb
+++ b/app/models/issue_query.rb
@@ -353,6 +353,9 @@ class IssueQuery < Query
     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