aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
-rw-r--r--activerecord/CHANGELOG7
-rwxr-xr-xactiverecord/lib/active_record.rb2
-rwxr-xr-xactiverecord/lib/active_record/base.rb55
-rw-r--r--activerecord/lib/active_record/calculations.rb197
-rw-r--r--activerecord/test/calculations_test.rb136
-rw-r--r--activerecord/test/fixtures/accounts.yml5
-rw-r--r--activesupport/lib/active_support/ordered_options.rb34
7 files changed, 370 insertions, 66 deletions
diff --git a/activerecord/CHANGELOG b/activerecord/CHANGELOG
index 096ae9eb4e..af0b44e3ce 100644
--- a/activerecord/CHANGELOG
+++ b/activerecord/CHANGELOG
@@ -1,5 +1,12 @@
*SVN*
+* 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]. Examples:
+
+ Person.average :age
+ Person.minimum :age
+ Person.maximum :age
+ Person.sum :salary, :group => :last_name
+
* Renamed Errors#count to Errors#size but kept an alias for the old name (and included an alias for length too) #3920 [contact@lukeredpath.co.uk]
* Reflections don't attempt to resolve module nesting of association classes. Simplify type computation. [Jeremy Kemper]
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
diff --git a/activerecord/test/calculations_test.rb b/activerecord/test/calculations_test.rb
new file mode 100644
index 0000000000..f4bc4f3c5b
--- /dev/null
+++ b/activerecord/test/calculations_test.rb
@@ -0,0 +1,136 @@
+require 'abstract_unit'
+require 'fixtures/company'
+require 'fixtures/topic'
+
+Company.has_many :accounts
+
+class CalculationsTest < Test::Unit::TestCase
+ fixtures :companies, :accounts, :topics
+
+ def test_should_sum_field
+ assert_equal 265, Account.sum(:credit_limit)
+ end
+
+ def test_should_average_field
+ value = Account.average(:credit_limit)
+ assert_equal 53, value
+ assert_kind_of Float, value
+ end
+
+ def test_should_get_maximum_of_field
+ assert_equal 60, Account.maximum(:credit_limit)
+ end
+
+ def test_should_get_minimum_of_field
+ assert_equal 50, Account.minimum(:credit_limit)
+ end
+
+ def test_should_group_by_field
+ c = Account.sum(:credit_limit, :group => :firm_id)
+ %w( 1 6 2 ).each { |firm_id| assert c.keys.include?(firm_id) }
+ end
+
+ def test_should_group_by_summed_field
+ c = Account.sum(:credit_limit, :group => :firm_id)
+ assert_equal 50, c['1']
+ assert_equal 105, c['6']
+ assert_equal 60, c['2']
+ end
+
+ def test_should_group_by_summed_field_having_condition
+ c = Account.sum(:credit_limit, :group => :firm_id,
+ :having => 'sum(credit_limit) > 50')
+ assert_nil c['1']
+ assert_equal 105, c['6']
+ assert_equal 60, c['2']
+ end
+
+ def test_should_group_by_summed_association
+ c = Account.sum(:credit_limit, :group => :firm)
+ assert_equal 50, c[companies(:first_firm)]
+ assert_equal 105, c[companies(:rails_core)]
+ assert_equal 60, c[companies(:first_client)]
+ end
+
+ def test_should_sum_field_with_conditions
+ assert_equal 105, Account.sum(:credit_limit, :conditions => 'firm_id = 6')
+ end
+
+ def test_should_group_by_summed_field_with_conditions
+ c = Account.sum(:credit_limit, :conditions => 'firm_id > 1',
+ :group => :firm_id)
+ assert_nil c['1']
+ assert_equal 105, c['6']
+ assert_equal 60, c['2']
+ end
+
+ def test_should_group_by_summed_field_with_conditions_and_having
+ c = Account.sum(:credit_limit, :conditions => 'firm_id > 1',
+ :group => :firm_id,
+ :having => 'sum(credit_limit) > 60')
+ assert_nil c['1']
+ assert_equal 105, c['6']
+ assert_nil c['2']
+ end
+
+ def test_should_group_by_fields_with_table_alias
+ c = Account.sum(:credit_limit, :group => 'accounts.firm_id')
+ assert_equal 50, c['1']
+ assert_equal 105, c['6']
+ assert_equal 60, c['2']
+ end
+
+ def test_should_calculate_with_invalid_field
+ assert_equal 5, Account.calculate(:count, '*')
+ assert_equal 5, Account.calculate(:count, :all)
+ end
+
+ def test_should_calculate_grouped_with_invalid_field
+ c = Account.count(:all, :group => 'accounts.firm_id')
+ assert_equal 1, c['1']
+ assert_equal 2, c['6']
+ assert_equal 1, c['2']
+ end
+
+ def test_should_calculate_grouped_association_with_invalid_field
+ c = Account.count(:all, :group => :firm)
+ assert_equal 1, c[companies(:first_firm)]
+ assert_equal 2, c[companies(:rails_core)]
+ assert_equal 1, c[companies(:first_client)]
+ end
+
+ def test_should_calculate_grouped_by_function
+ c = Company.count(:all, :group => 'UPPER(type)')
+ assert_equal 2, c[nil]
+ assert_equal 1, c['DEPENDENTFIRM']
+ assert_equal 3, c['CLIENT']
+ assert_equal 2, c['FIRM']
+ end
+
+ def test_should_calculate_grouped_by_function_with_table_alias
+ c = Topic.count(:all, :group => 'DATE(topics.written_on)')
+ assert_equal 1, c["2003-07-15"]
+ assert_equal 1, c["2003-07-16"]
+ end
+
+ def test_should_sum_scoped_field
+ assert_equal 15, companies(:rails_core).companies.sum(:id)
+ end
+
+ def test_should_sum_scoped_field_with_conditions
+ assert_equal 8, companies(:rails_core).companies.sum(:id, :conditions => 'id > 7')
+ end
+
+ def test_should_group_by_scoped_field
+ c = companies(:rails_core).companies.sum(:id, :group => :name)
+ assert_equal 7, c['Leetsoft']
+ assert_equal 8, c['Jadedpixel']
+ end
+
+ def test_should_group_by_summed_field_with_conditions_and_having
+ c = companies(:rails_core).companies.sum(:id, :group => :name,
+ :having => 'sum(id) > 7')
+ assert_nil c['Leetsoft']
+ assert_equal 8, c['Jadedpixel']
+ end
+end
diff --git a/activerecord/test/fixtures/accounts.yml b/activerecord/test/fixtures/accounts.yml
index 707c5d0a7f..a3d6742d79 100644
--- a/activerecord/test/fixtures/accounts.yml
+++ b/activerecord/test/fixtures/accounts.yml
@@ -16,3 +16,8 @@ last_account:
id: 4
firm_id: 2
credit_limit: 60
+
+rails_core_account_2:
+ id: 5
+ firm_id: 6
+ credit_limit: 55 \ No newline at end of file
diff --git a/activesupport/lib/active_support/ordered_options.rb b/activesupport/lib/active_support/ordered_options.rb
index ed5d4d3167..0e97578b3e 100644
--- a/activesupport/lib/active_support/ordered_options.rb
+++ b/activesupport/lib/active_support/ordered_options.rb
@@ -1,7 +1,5 @@
-class OrderedOptions < Array #:nodoc:
- def []=(key, value)
- key = key.to_sym
-
+class OrderedHash < Array #:nodoc:
+ def []=(key, value)
if pair = find_pair(key)
pair.pop
pair << value
@@ -11,16 +9,12 @@ class OrderedOptions < Array #:nodoc:
end
def [](key)
- pair = find_pair(key.to_sym)
+ pair = find_pair(key)
pair ? pair.last : nil
end
- def method_missing(name, *args)
- if name.to_s =~ /(.*)=$/
- self[$1.to_sym] = args.first
- else
- self[name]
- end
+ def keys
+ self.collect { |i| i.first }
end
private
@@ -28,4 +22,22 @@ class OrderedOptions < Array #:nodoc:
self.each { |i| return i if i.first == key }
return false
end
+end
+
+class OrderedOptions < OrderedHash #:nodoc:
+ def []=(key, value)
+ super(key.to_sym, value)
+ end
+
+ def [](key)
+ super(key.to_sym)
+ end
+
+ def method_missing(name, *args)
+ if name.to_s =~ /(.*)=$/
+ self[$1.to_sym] = args.first
+ else
+ self[name]
+ end
+ end
end \ No newline at end of file