123456789_123456789_123456789_123456789_123456789_

Module: ActiveRecord::Calculations

Relationships & Source Files
Namespace Children
Classes:
ColumnAliasTracker
Extension / Inclusion / Inheritance Descendants
Included In:
AssociationRelation, Associations::CollectionProxy, DisableJoinsAssociationRelation, Relation
Defined in: activerecord/lib/active_record/relation/calculations.rb

Instance Method Summary

Instance Method Details

#aggregate_column(column_name) (private)

[ GitHub ] 

  


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



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 414



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 409



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 363



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 334



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 182



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 656



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



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 217



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, loads all records in the relation, if the relation hasn’t been loaded yet. Calls the block with each record in the relation. 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



Note: 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 449



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 509



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
  group_fields = 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 = 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 = except(:group).distinct!(false)
    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 = 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?
    ActiveRecord::Result.empty
  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 418



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 371



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 596



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 422



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 352



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 291



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 639



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, loads all records in the relation, if the relation hasn’t been loaded yet. Calls the block with each record in the relation. Returns the sum of initial_value_or_column and the block return values:



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



Note: 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 172



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 621



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 604



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 591



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