123456789_123456789_123456789_123456789_123456789_

Module: ActiveRecord::Calculations

Relationships & Source Files
Namespace Children
Classes:
Extension / Inclusion / Inheritance Descendants
Included In:
Defined in: activerecord/lib/active_record/relation/calculations.rb

Instance Method Summary

Instance Method Details

#aggregate_column(column_name) (protected)

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 413

def aggregate_column(column_name)
  case column_name
  when Arel::Expressions
    column_name
  when :all
    Arel.star
  else
    arel_column(column_name)
  end
end

#all_attributes?(column_names) ⇒ Boolean (private)

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 425

def all_attributes?(column_names)
  (column_names.map(&:to_s) - model.attribute_names - model.attribute_aliases.keys).empty?
end

#async_average(column_name)

Same as #average, but performs the query asynchronously and returns an Promise.

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 122

def async_average(column_name)
  async.average(column_name)
end

#async_count(column_name = nil)

Same as #count, but performs the query asynchronously and returns an Promise.

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 108

def async_count(column_name = nil)
  async.count(column_name)
end

#async_ids

Same as #ids, but performs the query asynchronously and returns an Promise.

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 408

def async_ids
  async.ids
end

#async_maximum(column_name)

Same as #maximum, but performs the query asynchronously and returns an Promise.

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 152

def async_maximum(column_name)
  async.maximum(column_name)
end

#async_minimum(column_name)

Same as #minimum, but performs the query asynchronously and returns an Promise.

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 137

def async_minimum(column_name)
  async.minimum(column_name)
end

#async_pick(*column_names)

Same as #pick, but performs the query asynchronously and returns an Promise.

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 362

def async_pick(*column_names)
  async.pick(*column_names)
end

#async_pluck(*column_names)

Same as #pluck, but performs the query asynchronously and returns an Promise.

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 333

def async_pluck(*column_names)
  async.pluck(*column_names)
end

#async_sum(identity_or_column = nil)

Same as #sum, but performs the query asynchronously and returns an Promise.

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 181

def async_sum(identity_or_column = nil)
  async.sum(identity_or_column)
end

#average(column_name)

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
[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 116

def average(column_name)
  calculate(:average, column_name)
end

#build_count_subquery(relation, column_name, distinct) (private)

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 661

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 = [ relation.aggregate_column(column_name).as(column_alias) ]
  end

  subquery_alias = Arel.sql("subquery_for_count", retryable: true)
  select_value = operation_over_aggregate_column(column_alias, "count", false)

  if column_name == :all
    relation.unscope(:order).build_subquery(subquery_alias, select_value)
  else
    relation.build_subquery(subquery_alias, select_value)
  end
end

#build_count_subquery?(operation, column_name, distinct) ⇒ Boolean (private)

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 654

def build_count_subquery?(operation, column_name, distinct)
  # SQLite and older MySQL does not support `COUNT DISTINCT` with `*` or
  # multiple columns, so we need to use subquery for this.
  operation == "count" &&
    (((column_name == :all || select_values.many?) && distinct) || has_limit_or_offset?)
end

#calculate(operation, column_name)

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
[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 216

def calculate(operation, column_name)
  operation = operation.to_s.downcase

  if @none
    case operation
    when "count", "sum"
      result = group_values.any? ? Hash.new : 0
      return @async ? Promise::Complete.new(result) : result
    when "average", "minimum", "maximum"
      result = group_values.any? ? Hash.new : nil
      return @async ? Promise::Complete.new(result) : result
    end
  end

  if has_include?(column_name)
    relation = apply_join_dependency

    if operation == "count"
      unless distinct_value || distinct_select?(column_name || select_for_count)
        relation.distinct!
        relation.select_values = Array(model.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)

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", "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.

When given a block, calls the block with each record in the relation and returns the number of records for which the block returns a truthy value.

Person.count { |person| person.age > 21 }
# => counts the number of people older that 21

If the relation hasn’t been loaded yet, calling count with a block will load all records in the relation. If there are a lot of records in the relation, loading all records could result in performance issues.

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 94

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

#distinct_select?(column_name) ⇒ Boolean (private)

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 460

def distinct_select?(column_name)
  column_name.is_a?(::String) && /\bDISTINCT[\s(]/i.match?(column_name)
end

#execute_grouped_calculation(operation, column_name, distinct) (private)

This method is for internal use only.
[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 513

def execute_grouped_calculation(operation, column_name, distinct) # :nodoc:
  group_fields = group_values
  group_fields = group_fields.uniq if group_fields.size > 1

  if group_fields.size == 1 && group_fields.first.respond_to?(:to_sym)
    association  = model._reflect_on_association(group_fields.first)
    associated   = association && association.belongs_to? # only count belongs_to associations
    group_fields = Array(association.foreign_key) if associated
  end

  relation = except(:group).distinct!(false)
  group_fields = relation.arel_columns(group_fields)

  model.with_connection do |connection|
    column_alias_tracker = ColumnAliasTracker.new(connection)

    group_aliases = group_fields.map { |field|
      field = connection.visitor.compile(field) if Arel.arel_node?(field)
      column_alias_tracker.alias_for(field.to_s.downcase)
    }
    group_columns = group_aliases.zip(group_fields)

    column = relation.aggregate_column(column_name)
    column_alias = column_alias_tracker.alias_for("#{operation} #{column_name.to_s.downcase}")
    select_value = operation_over_aggregate_column(column, operation, distinct)
    select_value.as(model.adapter_class.quote_column_name(column_alias))

    select_values = [select_value]
    select_values += self.select_values unless having_clause.empty?

    select_values.concat group_columns.map { |aliaz, field|
      aliaz = model.adapter_class.quote_column_name(aliaz)
      if field.respond_to?(:as)
        field.as(aliaz)
      else
        "#{field} AS #{aliaz}"
      end
    }

    relation.group_values  = group_fields
    relation.select_values = select_values

    result = skip_query_cache_if_necessary do
      connection.select_all(relation.arel, "#{model.name} #{operation.capitalize}", async: @async)
    end

    result.then do |calculated_data|
      if association
        key_ids     = calculated_data.collect { |row| row[group_aliases.first] }
        key_records = association.klass.base_class.where(association.klass.base_class.primary_key => key_ids)
        key_records = key_records.index_by(&:id)
      end

      key_types = group_columns.each_with_object({}) do |(aliaz, col_name), types|
        types[aliaz] = col_name.try(:type_caster) ||
          type_for(col_name) do
            calculated_data.column_types.fetch(aliaz, Type.default_value)
          end
      end

      hash_rows = calculated_data.cast_values(key_types).map! do |row|
        calculated_data.columns.each_with_object({}).with_index do |(col_name, hash), i|
          hash[col_name] = row[i]
        end
      end

      if operation != "count"
        type = column.try(:type_caster) ||
          lookup_cast_type_from_join_dependencies(column_name.to_s) || Type.default_value
        type = type.subtype if Enum::EnumType === type
      end

      hash_rows.each_with_object({}) do |row, result|
        key = group_aliases.map { |aliaz| row[aliaz] }
        key = key.first if key.size == 1
        key = key_records[key] if associated

        result[key] = type_cast_calculated_value(row[column_alias], operation, type)
      end
    end
  end
end

#execute_simple_calculation(operation, column_name, distinct) (private)

This method is for internal use only.
[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 468

def execute_simple_calculation(operation, column_name, distinct) # :nodoc:
  if build_count_subquery?(operation, column_name, distinct)
    # Shortcut when limit is zero.
    return 0 if limit_value == 0

    relation = self
    query_builder = build_count_subquery(spawn, column_name, distinct)
  else
    # PostgreSQL doesn't like ORDER BY when there are no GROUP BY
    relation = unscope(:order).distinct!(false)

    column = relation.aggregate_column(column_name)
    select_value = operation_over_aggregate_column(column, operation, distinct)
    select_value.distinct = true if operation == "sum" && distinct

    relation.select_values = [select_value]

    query_builder = relation.arel
  end

  query_result = if relation.where_clause.contradiction?
    if @async
      FutureResult.wrap(ActiveRecord::Result.empty)
    else
      ActiveRecord::Result.empty
    end
  else
    skip_query_cache_if_necessary do
      model.with_connection do |c|
        c.select_all(query_builder, "#{model.name} #{operation.capitalize}", async: @async)
      end
    end
  end

  query_result.then do |result|
    if operation != "count"
      type = column.try(:type_caster) ||
        lookup_cast_type_from_join_dependencies(column_name.to_s) || Type.default_value
      type = type.subtype if Enum::EnumType === type
    end

    type_cast_calculated_value(result.cast_values.first, operation, type)
  end
end

#has_include?(column_name) ⇒ Boolean (private)

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 429

def has_include?(column_name)
  eager_loading? || (includes_values.present? && column_name && column_name != :all)
end

#ids

Returns the base model’s ID’s for the relation using the table’s primary key

Person.ids # SELECT people.id FROM people
Person.joins(:company).ids # SELECT people.id FROM people INNER JOIN companies ON companies.id = people.company_id
[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 370

def ids
  primary_key_array = Array(primary_key)

  if loaded?
    result = records.map do |record|
      if primary_key_array.one?
        record._read_attribute(primary_key_array.first)
      else
        primary_key_array.map { |column| record._read_attribute(column) }
      end
    end
    return @async ? Promise::Complete.new(result) : result
  end

  if has_include?(primary_key)
    relation = apply_join_dependency.group(*primary_key_array)
    return relation.ids
  end

  columns = arel_columns(primary_key_array)
  relation = spawn
  relation.select_values = columns

  result = if relation.where_clause.contradiction?
    ActiveRecord::Result.empty
  else
    skip_query_cache_if_necessary do
      model.with_connection do |c|
        c.select_all(relation, "#{model.name} Ids", async: @async)
      end
    end
  end

  result.then { |result| type_cast_pluck_values(result, columns) }
end

#lookup_cast_type_from_join_dependencies(name, join_dependencies = build_join_dependencies) (private)

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 601

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

#maximum(column_name)

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
[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 146

def maximum(column_name)
  calculate(:maximum, column_name)
end

#minimum(column_name)

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
[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 131

def minimum(column_name)
  calculate(:minimum, column_name)
end

#operation_over_aggregate_column(column, operation, distinct) (private)

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 464

def operation_over_aggregate_column(column, operation, distinct)
  operation == "count" ? column.count(distinct) : column.public_send(operation)
end

#perform_calculation(operation, column_name) (private)

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 433

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

#pick(*column_names)

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' ]
[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 351

def pick(*column_names)
  if loaded? && all_attributes?(column_names)
    result = records.pick(*column_names)
    return @async ? Promise::Complete.new(result) : result
  end

  limit(1).pluck(*column_names).then(&:first)
end

#pluck(*column_names)

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]

Comment.joins(:person).pluck(:id, person: :id)
# SELECT comments.id, person.id FROM comments INNER JOIN people person ON person.id = comments.person_id
# => [[1, 2], [2, 2]]

Comment.joins(:person).pluck(:id, person: [:id, :name])
# SELECT comments.id, person.id, person.name FROM comments INNER JOIN people person ON person.id = comments.person_id
# => [[1, 2, 'David'], [2, 2, 'David']]

Person.pluck(Arel.sql('DATEDIFF(updated_at, created_at)'))
# SELECT DATEDIFF(updated_at, created_at) FROM people
# => ['0', '27761', '173']

See also #ids.

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 290

def pluck(*column_names)
  if @none
    if @async
      return Promise::Complete.new([])
    else
      return []
    end
  end

  if loaded? && all_attributes?(column_names)
    result = records.pluck(*column_names)
    if @async
      return Promise::Complete.new(result)
    else
      return result
    end
  end

  if has_include?(column_names.first)
    relation = apply_join_dependency
    relation.pluck(*column_names)
  else
    model.disallow_raw_sql!(flattened_args(column_names))
    relation = spawn
    columns = relation.arel_columns(column_names)
    relation.select_values = columns
    result = skip_query_cache_if_necessary do
      if where_clause.contradiction?
        ActiveRecord::Result.empty(async: @async)
      else
        model.with_connection do |c|
          c.select_all(relation.arel, "#{model.name} Pluck", async: @async)
        end
      end
    end
    result.then do |result|
      type_cast_pluck_values(result, columns)
    end
  end
end

#select_for_count (private)

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 644

def select_for_count
  if select_values.empty?
    :all
  else
    with_connection do |conn|
      arel_columns(select_values).map { |column| conn.visitor.compile(column) }.join(", ")
    end
  end
end

#sum(initial_value_or_column = 0, &block)

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

When given a block, calls the block with each record in the relation and returns the sum of initial_value_or_column plus the block return values:

Person.sum { |person| person.age } # => 4562
Person.sum(1000) { |person| person.age } # => 5562

If the relation hasn’t been loaded yet, calling sum with a block will load all records in the relation. If there are a lot of records in the relation, loading all records could result in performance issues.

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 171

def sum(initial_value_or_column = 0, &block)
  if block_given?
    map(&block).sum(initial_value_or_column)
  else
    calculate(:sum, initial_value_or_column)
  end
end

#type_cast_calculated_value(value, operation, type) (private)

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 626

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) (private)

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 609

def type_cast_pluck_values(result, columns)
  cast_types = if result.columns.size != columns.size
    model.attribute_types
  else
    join_dependencies = nil
    columns.map.with_index do |column, i|
      column.try(:type_caster) ||
        model.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[i] || Type.default_value
        end
    end
  end
  result.cast_values(cast_types)
end

#type_for(field, &block) (private)

[ GitHub ]

  
# File 'activerecord/lib/active_record/relation/calculations.rb', line 596

def type_for(field, &block)
  field_name = field.respond_to?(:name) ? field.name.to_s : field.to_s.split(".").last
  model.type_for_attribute(field_name, &block)
end