diff options
Diffstat (limited to 'activerecord')
| -rw-r--r-- | activerecord/CHANGELOG | 7 | ||||
| -rwxr-xr-x | activerecord/lib/active_record.rb | 2 | ||||
| -rwxr-xr-x | activerecord/lib/active_record/base.rb | 55 | ||||
| -rw-r--r-- | activerecord/lib/active_record/calculations.rb | 197 | ||||
| -rw-r--r-- | activerecord/test/calculations_test.rb | 136 | ||||
| -rw-r--r-- | activerecord/test/fixtures/accounts.yml | 5 |
6 files changed, 347 insertions, 55 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 |