Added calculations: Base.count, Base.average, Base.sum, Base.minimum, Base.maxmium, and the generic Base.calculate. All can be used with :group and :having. Calculations and statitics need no longer require custom SQL. #3958 [Rick Olson]

git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@3646 5ecf4fe2-1ee6-0310-87b1-e25e094e27de

3 files changed, 199 insertions, 55 deletions

diff --git a/activerecord/lib/active_record.rb b/activerecord/lib/active_record.rb
index 477ab24f1d..ba306c0dfc 100755
--- a/activerecord/lib/active_record.rb
+++ b/activerecord/lib/active_record.rb
@@ -49,6 +49,7 @@ require 'active_record/acts/nested_set'
 require 'active_record/locking'
 require 'active_record/migration'
 require 'active_record/schema'
+require 'active_record/calculations'
 
 ActiveRecord::Base.class_eval do
   include ActiveRecord::Validations
@@ -63,6 +64,7 @@ ActiveRecord::Base.class_eval do
   include ActiveRecord::Acts::Tree
   include ActiveRecord::Acts::List
   include ActiveRecord::Acts::NestedSet
+  include ActiveRecord::Calculations
 end
 
 unless defined?(RAILS_CONNECTION_ADAPTERS)
diff --git a/activerecord/lib/active_record/base.rb b/activerecord/lib/active_record/base.rb
index 681f99b5c0..dfa9bfab9d 100755
--- a/activerecord/lib/active_record/base.rb
+++ b/activerecord/lib/active_record/base.rb
@@ -495,61 +495,6 @@ module ActiveRecord #:nodoc:
         connection.delete(sql, "#{name} Delete all")
       end
 
-      # Count operates using three different approaches. 
-      #
-      # * Count all: By not passing any parameters to count, it will return a count of all the rows for the model.
-      # * Count by conditions or joins: For backwards compatibility, you can pass in +conditions+ and +joins+ as individual parameters.
-      # * Count using options will find the row count matched by the options used.
-      #
-      # The last approach, count using options, accepts an option hash as the only parameter. The options are:
-      #
-      # * <tt>:conditions</tt>: An SQL fragment like "administrator = 1" or [ "user_name = ?", username ]. See conditions in the intro.
-      # * <tt>:joins</tt>: An SQL fragment for additional joins like "LEFT JOIN comments ON comments.post_id = id". (Rarely needed).
-      #   The records will be returned read-only since they will have attributes that do not correspond to the table's columns.
-      # * <tt>:include</tt>: Named associations that should be loaded alongside using LEFT OUTER JOINs. The symbols named refer
-      #   to already defined associations. When using named associations count returns the number DISTINCT items for the model you're counting.
-      #   See eager loading under Associations.
-      #
-      # Examples for counting all:
-      #   Person.count         # returns the total count of all people
-      #
-      # Examples for count by +conditions+ and +joins+ (for backwards compatibility):
-      #   Person.count("age > 26")  # returns the number of people older than 26
-      #   Person.find("age > 26 AND job.salary > 60000", "LEFT JOIN jobs on jobs.person_id = person.id") # returns the total number of rows matching the conditions and joins fetched by SELECT COUNT(*).
-      #
-      # Examples for count with options:
-      #   Person.count(:conditions => "age > 26")
-      #   Person.count(:conditions => "age > 26 AND job.salary > 60000", :include => :job) # because of the named association, it finds the DISTINCT count using LEFT OUTER JOIN.
-      #   Person.count(:conditions => "age > 26 AND job.salary > 60000", :joins => "LEFT JOIN jobs on jobs.person_id = person.id") # finds the number of rows matching the conditions and joins. 
-      def count(*args) 
-        options = {}
-        
-        #For backwards compatibility, we need to handle both count(conditions=nil, joins=nil) or count(options={}).
-        if args.size >= 0 and args.size <= 2
-          if args.first.is_a?(Hash)
-            options = args.first
-            #should we verify the options hash???
-          else
-            #Handle legacy paramter options: def count(conditions=nil, joins=nil) 
-            options.merge!(:conditions => args[0]) if args.length > 0
-            options.merge!(:joins => args[1]) if args.length > 1
-          end
-        else
-          raise(ArgumentError, "Unexpected parameters passed to count(*args): expected either count(conditions=nil, joins=nil) or count(options={})")
-        end
-        
-        options[:include] ? count_with_associations(options) : count_by_sql(construct_counter_sql(options))
-      end
-      
-      def construct_counter_sql(options)
-        sql  = "SELECT COUNT(" 
-        sql << "DISTINCT " if options[:distinct]
-        sql << "#{options[:select] || "#{table_name}.#{primary_key}"}) FROM #{table_name} "
-        sql << " #{options[:joins]} " if options[:joins]
-        add_conditions!(sql, options[:conditions])
-        sql
-      end
-
       # Returns the result of an SQL statement that should only include a COUNT(*) in the SELECT part.
       #   Product.count_by_sql "SELECT COUNT(*) FROM sales s, customers c WHERE s.customer_id = c.id"
       def count_by_sql(sql)
diff --git a/activerecord/lib/active_record/calculations.rb b/activerecord/lib/active_record/calculations.rb
new file mode 100644
index 0000000000..daed53d60c
--- /dev/null
+++ b/activerecord/lib/active_record/calculations.rb
@@ -0,0 +1,197 @@
+module ActiveRecord
+  module Calculations
+    def self.included(base)
+      base.extend(ClassMethods)
+    end
+
+    module ClassMethods
+      # Count operates using three different approaches. 
+      #
+      # * Count all: By not passing any parameters to count, it will return a count of all the rows for the model.
+      # * Count by conditions or joins: For backwards compatibility, you can pass in +conditions+ and +joins+ as individual parameters.
+      # * Count using options will find the row count matched by the options used.
+      #
+      # The last approach, count using options, accepts an option hash as the only parameter. The options are:
+      #
+      # * <tt>:conditions</tt>: An SQL fragment like "administrator = 1" or [ "user_name = ?", username ]. See conditions in the intro.
+      # * <tt>:joins</tt>: An SQL fragment for additional joins like "LEFT JOIN comments ON comments.post_id = id". (Rarely needed).
+      #   The records will be returned read-only since they will have attributes that do not correspond to the table's columns.
+      # * <tt>:include</tt>: Named associations that should be loaded alongside using LEFT OUTER JOINs. The symbols named refer
+      #   to already defined associations. When using named associations count returns the number DISTINCT items for the model you're counting.
+      #   See eager loading under Associations.
+      #
+      # Examples for counting all:
+      #   Person.count         # returns the total count of all people
+      #
+      # Examples for count by +conditions+ and +joins+ (for backwards compatibility):
+      #   Person.count("age > 26")  # returns the number of people older than 26
+      #   Person.find("age > 26 AND job.salary > 60000", "LEFT JOIN jobs on jobs.person_id = person.id") # returns the total number of rows matching the conditions and joins fetched by SELECT COUNT(*).
+      #
+      # Examples for count with options:
+      #   Person.count(:conditions => "age > 26")
+      #   Person.count(:conditions => "age > 26 AND job.salary > 60000", :include => :job) # because of the named association, it finds the DISTINCT count using LEFT OUTER JOIN.
+      #   Person.count(:conditions => "age > 26 AND job.salary > 60000", :joins => "LEFT JOIN jobs on jobs.person_id = person.id") # finds the number of rows matching the conditions and joins. 
+      #   Person.count('id', :conditions => "age > 26") # Performs a COUNT(id)
+      #   Person.count(:all, :conditions => "age > 26") # Performs a COUNT(*) (:all is an alias for '*')
+      #
+      # Note: Person.count(:all) will not work because it will use :all as the condition.  Use Person.count instead.
+      def count(*args)
+        options    = {}
+        column_name = :all
+        
+        #For backwards compatibility, we need to handle both count(conditions=nil, joins=nil) or count(options={}).
+        if args.size >= 0 and args.size <= 2
+          if args.first.is_a?(Hash)
+            options = args.first
+            #should we verify the options hash???
+          elsif args[1].is_a?(Hash)
+            column_name = args.first
+            options    = args[1]
+          else
+            # Handle legacy paramter options: def count(conditions=nil, joins=nil) 
+            options.merge!(:conditions => args[0]) if args.length > 0
+            options.merge!(:joins => args[1]) if args.length > 1
+          end
+        else
+          raise(ArgumentError, "Unexpected parameters passed to count(*args): expected either count(conditions=nil, joins=nil) or count(options={})")
+        end
+        
+        column_name = options[:select] if options[:select]
+        options[:include] ? count_with_associations(options) : calculate(:count, column_name, options)
+      end
+
+      # Calculates average value on a given column.  The value is returned as a float.  See #calculate for examples with options.
+      #
+      #   Person.average('age')
+      def average(column_name, options = {})
+        calculate(:avg, column_name, options)
+      end
+
+      # Calculates the minimum value on a given column.  The value is returned with the same data type of the column..  See #calculate for examples with options.
+      #
+      #   Person.minimum('age')
+      def minimum(column_name, options = {})
+        calculate(:min, column_name, options)
+      end
+
+      # Calculates the maximum value on a given column.  The value is returned with the same data type of the column..  See #calculate for examples with options.
+      #
+      #   Person.maximum('age')
+      def maximum(column_name, options = {})
+        calculate(:max, column_name, options)
+      end
+
+      # Calculates the sum value on a given column.  The value is returned with the same data type of the column..  See #calculate for examples with options.
+      #
+      #   Person.maximum('age')
+      def sum(column_name, options = {})
+        calculate(:sum, column_name, options)
+      end
+
+      # This calculates aggregate values in the given column:  Methods for count, sum, average, minimum, and maximum have been added as shortcuts.
+      # Options such as :conditions, :order, :group, :having, and :joins can be passed to customize the query.  
+      #
+      # There are two basic forms of output:
+      #   * Single aggregate value: The single value is type cast to Fixnum for COUNT, Float for AVG, and the given column's type for everything else.
+      #   * Grouped values: This returns an ordered hash of the values and groups them by the :group option.  It takes either a column name, or the name 
+      #     of a belongs_to association.
+      #
+      #       values = Person.maximum(:age, :group => 'last_name')
+      #       puts values["Drake"]
+      #       => 43
+      #
+      #       drake  = Family.find_by_last_name('Drake')
+      #       values = Person.maximum(:age, :group => :family) # Person belongs_to :family
+      #       puts values[drake]
+      #       => 43
+      #
+      #       values.each do |family, max_age|
+      #       ...
+      #       end
+      #
+      # Examples:
+      #   Person.calculate(:count, :all) # The same as Person.count
+      #   Person.average(:age) # SELECT AVG(age) FROM people...
+      #   Person.minimum(:age, :conditions => ['last_name != ?', 'Drake']) # Selects the minimum age for everyone with a last name other than 'Drake'
+      #   Person.minimum(:age, :having => 'min(age) > 17', :group => :last_name) # Selects the minimum age for any family without any minors
+      def calculate(operation, column_name, options = {})
+        column_name = '*' if column_name == :all
+        column     = columns.detect { |c| c.name.to_s == column_name.to_s }
+        if options[:group]
+          execute_grouped_calculation(operation, column_name, column, options)
+        else
+          execute_simple_calculation(operation, column_name, column, options)
+        end
+      end
+
+      protected
+      def construct_calculation_sql(operation, column_name, options)
+        sql  = ["SELECT #{operation}(#{'DISTINCT ' if options[:distinct]}#{column_name})"]
+        sql << ", #{options[:group_field]}" if options[:group]
+        sql << " FROM #{table_name} "
+        add_joins!(sql, options)
+        add_conditions!(sql, options[:conditions])
+        sql << " GROUP BY #{options[:group_field]}" if options[:group]
+        sql << " HAVING #{options[:having]}" if options[:group] && options[:having]
+        sql.join
+      end
+
+      def execute_simple_calculation(operation, column_name, column, options)
+        value  = connection.select_value(construct_calculation_sql(operation, column_name, options))
+        type_cast_calculated_value(value, column, operation)
+      end
+
+      def execute_grouped_calculation(operation, column_name, column, options)
+        group_attr      = options[:group].to_s
+        association     = reflect_on_association(group_attr.to_sym)
+        associated      = association && association.macro == :belongs_to # only count belongs_to associations
+        group_field     = (associated ? "#{options[:group]}_id" : options[:group]).to_s
+        sql             = construct_calculation_sql(operation, column_name, options.merge(:group_field => group_field))
+        calculated_data = connection.select_all(sql)
+
+        if association
+          key_ids     = calculated_data.collect { |row| row[group_field] }
+          key_records = ActiveRecord::Base.send(:class_of_active_record_descendant, association.klass).find(key_ids)
+          key_records = key_records.inject({}) { |hsh, r| hsh.merge(r.id => r) }
+        end
+
+        calculated_data.inject(OrderedHash.new) do |all, row|
+          key   = associated ? key_records[row[group_field].to_i] : row[column_key(group_field)]
+          value = row[column_key("#{operation}(#{column_name})")]
+          all << [key, type_cast_calculated_value(value, column, operation)]
+        end
+      end
+
+      private
+      # converts a given key to the value that the database adapter returns as
+      #
+      #   users.id #=> id
+      #   sum(id) #=> sum(id)
+      #
+      # psql strips off the () function too
+      # 
+      #   sum(id) #=> sum
+      #
+      # Should this go in a DB Adapter?
+      def column_key(key)
+        return key.split('.').last unless key =~ /\(/ # split off table alias
+        case connection
+          when ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
+            key.split('(').first.split('.').last
+          else
+            sql_func, sql_args = key.split('(')
+            "#{sql_func.split('.').last}(#{sql_args}"
+        end
+      end
+
+      def type_cast_calculated_value(value, column, operation)
+        operation = operation.to_s.downcase
+        case operation
+          when 'count' then value.to_i
+          when 'avg'   then value.to_f
+          else column ? column.type_cast(value) : value
+        end
+      end
+    end
+  end
+end
\ No newline at end of file