module ActiveRecord::Calculations
Public Instance Methods
Calculates the average value on a given column. Returns nil
if there's no row. See calculate
for examples with options.
Person.average(:age) # => 35.8
# File lib/active_record/relation/calculations.rb, line 59 def average(column_name) calculate(:average, column_name) end
This calculates aggregate values in the given column. Methods for count
, sum
, average
, minimum
, and maximum
have been added as shortcuts.
Person.calculate(:count, :all) # The same as Person.count Person.average(:age) # SELECT AVG(age) FROM people... # Selects the minimum age for any family without any minors Person.group(:last_name).having("min(age) > 17").minimum(:age) Person.sum("2 * age")
There are two basic forms of output:
-
Single aggregate value: The single value is type cast to Integer 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. It takes either a column name, or the name of a belongs_to association.
values = Person.group('last_name').maximum(:age) puts values["Drake"] # => 43 drake = Family.find_by(last_name: 'Drake') values = Person.group(:family).maximum(:age) # Person belongs_to :family puts values[drake] # => 43 values.each do |family, max_age| ... end
# File lib/active_record/relation/calculations.rb, line 129 def calculate(operation, column_name) if has_include?(column_name) relation = apply_join_dependency if operation.to_s.downcase == "count" unless distinct_value || distinct_select?(column_name || select_for_count) relation.distinct! relation.select_values = [ klass.primary_key || table[Arel.star] ] end # PostgreSQL: ORDER BY expressions must appear in SELECT list when using DISTINCT relation.order_values = [] if group_values.empty? end relation.calculate(operation, column_name) else perform_calculation(operation, column_name) end end
Count the records.
Person.count # => the total count of all people Person.count(:age) # => returns the total count of all people whose age is present in database Person.count(:all) # => performs a COUNT(*) (:all is an alias for '*') Person.distinct.count(:age) # => counts the number of different age values
If count
is used with Relation#group, it returns a Hash whose keys represent the aggregated column, and the values are the respective amounts:
Person.group(:city).count # => { 'Rome' => 5, 'Paris' => 3 }
If count
is used with Relation#group for multiple columns, it returns a Hash whose keys are an array containing the individual values of each column and the value of each key would be the count
.
Article.group(:status, :category).count # => {["draft", "business"]=>10, ["draft", "technology"]=>4, ["published", "business"]=>0, ["published", "technology"]=>2}
If count
is used with Relation#select, it will count the selected columns:
Person.select(:age).count # => counts the number of different age values
Note: not all valid Relation#select expressions are valid count
expressions. The specifics differ between databases. In invalid cases, an error from the database is thrown.
# File lib/active_record/relation/calculations.rb, line 43 def count(column_name = nil) if block_given? unless column_name.nil? raise ArgumentError, "Column name argument is not supported when a block is passed." end super() else calculate(:count, column_name) end end
Pluck all the ID's for the relation using the table's primary key
Person.ids # SELECT people.id FROM people Person.joins(:companies).ids # SELECT people.id FROM people INNER JOIN companies ON companies.person_id = people.id
# File lib/active_record/relation/calculations.rb, line 233 def ids pluck primary_key end
Calculates the maximum value on a given column. The value is returned with the same data type of the column, or nil
if there's no row. See calculate
for examples with options.
Person.maximum(:age) # => 93
# File lib/active_record/relation/calculations.rb, line 77 def maximum(column_name) calculate(:maximum, column_name) end
Calculates the minimum value on a given column. The value is returned with the same data type of the column, or nil
if there's no row. See calculate
for examples with options.
Person.minimum(:age) # => 7
# File lib/active_record/relation/calculations.rb, line 68 def minimum(column_name) calculate(:minimum, column_name) end
Pick the value(s) from the named column(s) in the current relation. This is short-hand for relation.limit(1).pluck(*column_names).first
, and is primarily useful when you have a relation that's already narrowed down to a single row.
Just like pluck
, pick
will only load the actual value, not the entire record object, so it's also more efficient. The value is, again like with pluck, typecast by the column type.
Person.where(id: 1).pick(:name) # SELECT people.name FROM people WHERE id = 1 LIMIT 1 # => 'David' Person.where(id: 1).pick(:name, :email_address) # SELECT people.name, people.email_address FROM people WHERE id = 1 LIMIT 1 # => [ 'David', 'david@loudthinking.com' ]
# File lib/active_record/relation/calculations.rb, line 221 def pick(*column_names) if loaded? && all_attributes?(column_names) return records.pick(*column_names) end limit(1).pluck(*column_names).first end
Use pluck
as a shortcut to select one or more attributes without loading a bunch of records just to grab the attributes you want.
Person.pluck(:name)
instead of
Person.all.map(&:name)
Pluck returns an Array of attribute values type-casted to match the plucked column names, if they can be deduced. Plucking an SQL fragment returns String values by default.
Person.pluck(:name) # SELECT people.name FROM people # => ['David', 'Jeremy', 'Jose'] Person.pluck(:id, :name) # SELECT people.id, people.name FROM people # => [[1, 'David'], [2, 'Jeremy'], [3, 'Jose']] Person.distinct.pluck(:role) # SELECT DISTINCT role FROM people # => ['admin', 'member', 'guest'] Person.where(age: 21).limit(5).pluck(:id) # SELECT people.id FROM people WHERE people.age = 21 LIMIT 5 # => [2, 3] Person.pluck(Arel.sql('DATEDIFF(updated_at, created_at)')) # SELECT DATEDIFF(updated_at, created_at) FROM people # => ['0', '27761', '173']
See also ids
.
# File lib/active_record/relation/calculations.rb, line 183 def pluck(*column_names) if loaded? && all_attributes?(column_names) return records.pluck(*column_names) end if has_include?(column_names.first) relation = apply_join_dependency relation.pluck(*column_names) else klass.disallow_raw_sql!(column_names) columns = arel_columns(column_names) relation = spawn relation.select_values = columns result = skip_query_cache_if_necessary do if where_clause.contradiction? ActiveRecord::Result.new([], []) else klass.connection.select_all(relation.arel, nil) end end type_cast_pluck_values(result, columns) end end
Calculates the sum of values on a given column. The value is returned with the same data type of the column, 0
if there's no row. See calculate
for examples with options.
Person.sum(:age) # => 4562
# File lib/active_record/relation/calculations.rb, line 86 def sum(column_name = nil) if block_given? unless column_name.nil? raise ArgumentError, "Column name argument is not supported when a block is passed." end super() else calculate(:sum, column_name) end end
Private Instance Methods
# File lib/active_record/relation/calculations.rb, line 277 def aggregate_column(column_name) return column_name if Arel::Expressions === column_name arel_column(column_name.to_s) do |name| Arel.sql(column_name == :all ? "*" : name) end end
# File lib/active_record/relation/calculations.rb, line 238 def all_attributes?(column_names) (column_names.map(&:to_s) - @klass.attribute_names - @klass.attribute_aliases.keys).empty? end
# File lib/active_record/relation/calculations.rb, line 470 def build_count_subquery(relation, column_name, distinct) if column_name == :all column_alias = Arel.star relation.select_values = [ Arel.sql(FinderMethods::ONE_AS_ONE) ] unless distinct else column_alias = Arel.sql("count_column") relation.select_values = [ aggregate_column(column_name).as(column_alias) ] end subquery_alias = Arel.sql("subquery_for_count") select_value = operation_over_aggregate_column(column_alias, "count", false) relation.build_subquery(subquery_alias, select_value) end
Converts the given field to the value that the database adapter returns as a usable column name:
column_alias_for("users.id") # => "users_id" column_alias_for("sum(id)") # => "sum_id" column_alias_for("count(distinct users.id)") # => "count_distinct_users_id" column_alias_for("count(*)") # => "count_all"
# File lib/active_record/relation/calculations.rb, line 408 def column_alias_for(field) column_alias = +field column_alias.gsub!(/\*/, "all") column_alias.gsub!(/\W+/, " ") column_alias.strip! column_alias.gsub!(/ +/, "_") connection.table_alias_for(column_alias) end
# File lib/active_record/relation/calculations.rb, line 273 def distinct_select?(column_name) column_name.is_a?(::String) && /\bDISTINCT[\s(]/i.match?(column_name) end
# File lib/active_record/relation/calculations.rb, line 242 def has_include?(column_name) eager_loading? || (includes_values.present? && column_name && column_name != :all) end
# File lib/active_record/relation/calculations.rb, line 423 def lookup_cast_type_from_join_dependencies(name, join_dependencies = build_join_dependencies) each_join_dependencies(join_dependencies) do |join| type = join.base_klass.attribute_types.fetch(name, nil) return type if type end nil end
# File lib/active_record/relation/calculations.rb, line 285 def operation_over_aggregate_column(column, operation, distinct) operation == "count" ? column.count(distinct) : column.public_send(operation) end
# File lib/active_record/relation/calculations.rb, line 246 def perform_calculation(operation, column_name) operation = operation.to_s.downcase # If #count is used with #distinct (i.e. `relation.distinct.count`) it is # considered distinct. distinct = distinct_value if operation == "count" column_name ||= select_for_count if column_name == :all if !distinct distinct = distinct_select?(select_for_count) if group_values.empty? elsif group_values.any? || select_values.empty? && order_values.empty? column_name = primary_key end elsif distinct_select?(column_name) distinct = nil end end if group_values.any? execute_grouped_calculation(operation, column_name, distinct) else execute_simple_calculation(operation, column_name, distinct) end end
# File lib/active_record/relation/calculations.rb, line 461 def select_for_count if select_values.present? return select_values.first if select_values.one? select_values.join(", ") else :all end end
# File lib/active_record/relation/calculations.rb, line 448 def type_cast_calculated_value(value, operation) case operation when "count" value.to_i when "sum" yield value || 0 when "average" value&.respond_to?(:to_d) ? value.to_d : value else # "minimum", "maximum" yield value end end
# File lib/active_record/relation/calculations.rb, line 431 def type_cast_pluck_values(result, columns) cast_types = if result.columns.size != columns.size klass.attribute_types else join_dependencies = nil columns.map.with_index do |column, i| column.try(:type_caster) || klass.attribute_types.fetch(name = result.columns[i]) do join_dependencies ||= build_join_dependencies lookup_cast_type_from_join_dependencies(name, join_dependencies) || result.column_types[name] || Type.default_value end end end result.cast_values(cast_types) end
# File lib/active_record/relation/calculations.rb, line 418 def type_for(field, &block) field_name = field.respond_to?(:name) ? field.name.to_s : field.to_s.split(".").last @klass.type_for_attribute(field_name, &block) end