module ActiveRecord::Calculations

Public Instance Methods

average(column_name) click to toggle source

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 100
def average(column_name)
  calculate(:average, column_name)
end
calculate(operation, column_name) click to toggle source

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 179
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(column_name = nil) click to toggle source

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.

Calls superclass method 
# File lib/active_record/relation/calculations.rb, line 84
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
ids() click to toggle source

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 283
def ids
  pluck primary_key
end
maximum(column_name) click to toggle source

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 118
def maximum(column_name)
  calculate(:maximum, column_name)
end
minimum(column_name) click to toggle source

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 109
def minimum(column_name)
  calculate(:minimum, column_name)
end
pick(*column_names) click to toggle source

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 271
def pick(*column_names)
  if loaded? && all_attributes?(column_names)
    return records.pick(*column_names)
  end

  limit(1).pluck(*column_names).first
end
pluck(*column_names) click to toggle source

Use pluck as a shortcut to select one or more attributes without loading an entire record object per row.

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 233
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.empty
      else
        klass.connection.select_all(relation.arel, "#{klass.name} Pluck")
      end
    end
    type_cast_pluck_values(result, columns)
  end
end
sum(identity_or_column = nil, &block) click to toggle source

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 127
    def sum(identity_or_column = nil, &block)
      if block_given?
        values = map(&block)
        if identity_or_column.nil? && (values.first.is_a?(Numeric) || values.first(1) == [] || values.first.respond_to?(:coerce))
          identity_or_column = 0
        end

        if identity_or_column.nil?
          ActiveSupport::Deprecation.warn(<<-MSG.squish)
            Rails 7.0 has deprecated Enumerable.sum in favor of Ruby's native implementation available since 2.4.
            Sum of non-numeric elements requires an initial argument.
          MSG
          values.inject(:+) || 0
        else
          values.sum(identity_or_column)
        end
      else
        calculate(:sum, identity_or_column)
      end
    end

Private Instance Methods

aggregate_column(column_name) click to toggle source 
# File lib/active_record/relation/calculations.rb, line 327
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
all_attributes?(column_names) click to toggle source 
# File lib/active_record/relation/calculations.rb, line 288
def all_attributes?(column_names)
  (column_names.map(&:to_s) - @klass.attribute_names - @klass.attribute_aliases.keys).empty?
end
build_count_subquery(relation, column_name, distinct) click to toggle source 
# File lib/active_record/relation/calculations.rb, line 502
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
distinct_select?(column_name) click to toggle source 
# File lib/active_record/relation/calculations.rb, line 323
def distinct_select?(column_name)
  column_name.is_a?(::String) && /\bDISTINCT[\s(]/i.match?(column_name)
end
has_include?(column_name) click to toggle source 
# File lib/active_record/relation/calculations.rb, line 292
def has_include?(column_name)
  eager_loading? || (includes_values.present? && column_name && column_name != :all)
end
lookup_cast_type_from_join_dependencies(name, join_dependencies = build_join_dependencies) click to toggle source 
# File lib/active_record/relation/calculations.rb, line 450
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
operation_over_aggregate_column(column, operation, distinct) click to toggle source 
# File lib/active_record/relation/calculations.rb, line 335
def operation_over_aggregate_column(column, operation, distinct)
  operation == "count" ? column.count(distinct) : column.public_send(operation)
end
perform_calculation(operation, column_name) click to toggle source 
# File lib/active_record/relation/calculations.rb, line 296
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
select_for_count() click to toggle source 
# File lib/active_record/relation/calculations.rb, line 493
def select_for_count
  if select_values.present?
    return select_values.first if select_values.one?
    select_values.join(", ")
  else
    :all
  end
end
type_cast_calculated_value(value, operation, type) click to toggle source 
# File lib/active_record/relation/calculations.rb, line 475
def type_cast_calculated_value(value, operation, type)
  case operation
  when "count"
    value.to_i
  when "sum"
    type.deserialize(value || 0)
  when "average"
    case type.type
    when :integer, :decimal
      value&.to_d
    else
      type.deserialize(value)
    end
  else # "minimum", "maximum"
    type.deserialize(value)
  end
end
type_cast_pluck_values(result, columns) click to toggle source 
# File lib/active_record/relation/calculations.rb, line 458
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
type_for(field, &block) click to toggle source 
# File lib/active_record/relation/calculations.rb, line 445
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