From 19d2ff83db5232a816dee201800baf3924705b31 Mon Sep 17 00:00:00 2001 From: Emilio Tagua Date: Wed, 29 Apr 2009 19:39:53 -0300 Subject: Calculations now use Arel to construct the query. Implemented other methods in AR::Base with Arel support. --- activerecord/lib/active_record/calculations.rb | 197 +++++++++++-------------- 1 file changed, 86 insertions(+), 111 deletions(-) (limited to 'activerecord/lib/active_record/calculations.rb') diff --git a/activerecord/lib/active_record/calculations.rb b/activerecord/lib/active_record/calculations.rb index f077818d3b..9dbfcdf175 100644 --- a/activerecord/lib/active_record/calculations.rb +++ b/activerecord/lib/active_record/calculations.rb @@ -54,7 +54,7 @@ module ActiveRecord # # Person.average('age') # => 35.8 def average(column_name, options = {}) - calculate(:avg, column_name, options) + calculate(:average, column_name, options) end # Calculates the minimum value on a given column. The value is returned @@ -63,7 +63,7 @@ module ActiveRecord # # Person.minimum('age') # => 7 def minimum(column_name, options = {}) - calculate(:min, column_name, options) + calculate(:minimum, column_name, options) end # Calculates the maximum value on a given column. The value is returned @@ -72,7 +72,7 @@ module ActiveRecord # # Person.maximum('age') # => 93 def maximum(column_name, options = {}) - calculate(:max, column_name, options) + calculate(:maximum, column_name, options) end # Calculates the sum of values on a given column. The value is returned @@ -124,19 +124,96 @@ module ActiveRecord # Person.sum("2 * age") def calculate(operation, column_name, options = {}) validate_calculation_options(operation, options) - column_name = options[:select] if options[:select] - column_name = '*' if column_name == :all - column = column_for column_name + + scope = scope(:find) + + merged_includes = merge_includes(scope ? scope[:include] : [], options[:include]) + joins = construct_join(options[:joins], scope) + + if merged_includes.any? + join_dependency = ActiveRecord::Associations::ClassMethods::JoinDependency.new(self, merged_includes, joins) + joins << join_dependency.join_associations.collect{|join| join.association_join }.join + end + + if operation == :count + if merged_includes.any? + distinct = true + column_name = options[:select] || primary_key + end + + distinct = nil if column_name.to_s =~ /\s*DISTINCT\s+/i + distinct ||= options[:distinct] + else + distinct = nil + end + catch :invalid_query do + conditions = construct_conditions(options[:conditions], scope) + conditions << construct_limited_ids_condition(conditions, options, join_dependency) if join_dependency && !using_limitable_reflections?(join_dependency.reflections) && ((scope && scope[:limit]) || options[:limit]) + if options[:group] - return execute_grouped_calculation(operation, column_name, column, options) + return execute_grouped_calculation(operation, column_name, options.merge(:merged_conditions => conditions, :merged_joins => joins, :distinct => distinct)) else - return execute_simple_calculation(operation, column_name, column, options) + return execute_simple_calculation(operation, column_name, options.merge(:merged_conditions => conditions, :merged_joins => joins, :distinct => distinct)) end end 0 end + def execute_simple_calculation(operation, column_name, options) #:nodoc: + table = options[:from] || table_name + value = if operation == :count + if column_name == :all && options[:select].blank? + column_name = "*" + elsif !options[:select].blank? + column_name = options[:select] + end + construct_finder_arel(options.merge(:select => Arel::Attribute.new(Arel(table), column_name).count(options[:distinct]))).select_value + else + construct_finder_arel(options.merge(:select => Arel::Attribute.new(Arel(table), column_name).send(operation))).select_value + end + + type_cast_calculated_value(value, column_for(column_name), operation) + end + + def execute_grouped_calculation(operation, column_name, options) #:nodoc: + 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 ? association.primary_key_name : group_attr + group_alias = column_alias_for(group_field) + group_column = column_for group_field + + options[:group] = connection.adapter_name == 'FrontBase' ? group_alias : group_field + + aggregate_alias = column_alias_for(operation, column_name) + if operation == :count && column_name == :all + options[:select] = "COUNT(*) AS count_all, #{group_field} AS #{group_alias}" + else + arel_column = Arel::Attribute.new(Arel(table_name), column_name).send(operation) + options[:select] = "#{arel_column.as(aggregate_alias).to_sql}, #{group_field} AS #{group_alias}" + end + + + sql = construct_finder_arel(options) + + calculated_data = connection.select_all(sql.to_sql) + + if association + key_ids = calculated_data.collect { |row| row[group_alias] } + key_records = association.klass.base_class.find(key_ids) + key_records = key_records.inject({}) { |hsh, r| hsh.merge(r.id => r) } + end + + calculated_data.inject(ActiveSupport::OrderedHash.new) do |all, row| + key = type_cast_calculated_value(row[group_alias], group_column) + key = key_records[key] if associated + value = row[aggregate_alias] + all[key] = type_cast_calculated_value(value, column_for(column_name), operation) + all + end + end + protected def construct_count_options_from_args(*args) options = {} @@ -167,108 +244,6 @@ module ActiveRecord [column_name || :all, options] end - def construct_calculation_sql(operation, column_name, options) #:nodoc: - operation = operation.to_s.downcase - options = options.symbolize_keys - - scope = scope(:find) - merged_includes = merge_includes(scope ? scope[:include] : [], options[:include]) - aggregate_alias = column_alias_for(operation, column_name) - column_name = "#{connection.quote_table_name(table_name)}.#{column_name}" if column_names.include?(column_name.to_s) - - if operation == 'count' - if merged_includes.any? - options[:distinct] = true - column_name = options[:select] || [connection.quote_table_name(table_name), primary_key] * '.' - end - - if options[:distinct] - use_workaround = !connection.supports_count_distinct? - end - end - - if options[:distinct] && column_name.to_s !~ /\s*DISTINCT\s+/i - distinct = 'DISTINCT ' - end - sql = "SELECT #{operation}(#{distinct}#{column_name}) AS #{aggregate_alias}" - - # A (slower) workaround if we're using a backend, like sqlite, that doesn't support COUNT DISTINCT. - sql = "SELECT COUNT(*) AS #{aggregate_alias}" if use_workaround - - sql << ", #{options[:group_field]} AS #{options[:group_alias]}" if options[:group] - if options[:from] - sql << " FROM #{options[:from]} " - else - sql << " FROM (SELECT #{distinct}#{column_name}" if use_workaround - sql << " FROM #{connection.quote_table_name(table_name)} " - end - - joins = "" - add_joins!(joins, options[:joins], scope) - - if merged_includes.any? - join_dependency = ActiveRecord::Associations::ClassMethods::JoinDependency.new(self, merged_includes, joins) - sql << join_dependency.join_associations.collect{|join| join.association_join }.join - end - - sql << joins unless joins.blank? - - add_conditions!(sql, options[:conditions], scope) - add_limited_ids_condition!(sql, options, join_dependency) if join_dependency && !using_limitable_reflections?(join_dependency.reflections) && ((scope && scope[:limit]) || options[:limit]) - - if options[:group] - group_key = connection.adapter_name == 'FrontBase' ? :group_alias : :group_field - sql << " GROUP BY #{options[group_key]} " - end - - if options[:group] && options[:having] - having = sanitize_sql_for_conditions(options[:having]) - - # FrontBase requires identifiers in the HAVING clause and chokes on function calls - if connection.adapter_name == 'FrontBase' - having.downcase! - having.gsub!(/#{operation}\s*\(\s*#{column_name}\s*\)/, aggregate_alias) - end - - sql << " HAVING #{having} " - end - - sql << " ORDER BY #{options[:order]} " if options[:order] - add_limit!(sql, options, scope) - sql << ") #{aggregate_alias}_subquery" if use_workaround - sql - end - - def execute_simple_calculation(operation, column_name, column, options) #:nodoc: - 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) #:nodoc: - 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 ? association.primary_key_name : group_attr - group_alias = column_alias_for(group_field) - group_column = column_for group_field - sql = construct_calculation_sql(operation, column_name, options.merge(:group_field => group_field, :group_alias => group_alias)) - calculated_data = connection.select_all(sql) - aggregate_alias = column_alias_for(operation, column_name) - - if association - key_ids = calculated_data.collect { |row| row[group_alias] } - key_records = association.klass.base_class.find(key_ids) - key_records = key_records.inject({}) { |hsh, r| hsh.merge(r.id => r) } - end - - calculated_data.inject(ActiveSupport::OrderedHash.new) do |all, row| - key = type_cast_calculated_value(row[group_alias], group_column) - key = key_records[key] if associated - value = row[aggregate_alias] - all[key] = type_cast_calculated_value(value, column, operation) - all - end - end private def validate_calculation_options(operation, options = {}) @@ -304,7 +279,7 @@ module ActiveRecord case operation when 'count' then value.to_i when 'sum' then type_cast_using_column(value || '0', column) - when 'avg' then value && (value.is_a?(Fixnum) ? value.to_f : value).to_d + when 'average' then value && (value.is_a?(Fixnum) ? value.to_f : value).to_d else type_cast_using_column(value, column) end end -- cgit v1.2.3