module ActiveRecord::Calculations
Active Record Calculations
Public instance methods
Same as average
, but performs the query asynchronously and returns an ActiveRecord::Promise
.
Source code GitHub
# File activerecord/lib/active_record/relation/calculations.rb, line 122
def async_average(column_name)
async.average(column_name)
end
Same as count
, but performs the query asynchronously and returns an ActiveRecord::Promise
.
Source code GitHub
# File activerecord/lib/active_record/relation/calculations.rb, line 108
def async_count(column_name = nil)
async.count(column_name)
end
Same as ids
, but performs the query asynchronously and returns an ActiveRecord::Promise
.
Source code GitHub
# File activerecord/lib/active_record/relation/calculations.rb, line 405
def async_ids
async.ids
end
Same as maximum
, but performs the query asynchronously and returns an ActiveRecord::Promise
.
Source code GitHub
# File activerecord/lib/active_record/relation/calculations.rb, line 152
def async_maximum(column_name)
async.maximum(column_name)
end
Same as minimum
, but performs the query asynchronously and returns an ActiveRecord::Promise
.
Source code GitHub
# File activerecord/lib/active_record/relation/calculations.rb, line 137
def async_minimum(column_name)
async.minimum(column_name)
end
Same as pick
, but performs the query asynchronously and returns an ActiveRecord::Promise
.
Source code GitHub
# File activerecord/lib/active_record/relation/calculations.rb, line 359
def async_pick(*column_names)
async.pick(*column_names)
end
Same as pluck
, but performs the query asynchronously and returns an ActiveRecord::Promise
.
Source code GitHub
# File activerecord/lib/active_record/relation/calculations.rb, line 330
def async_pluck(*column_names)
async.pluck(*column_names)
end
Same as sum
, but performs the query asynchronously and returns an ActiveRecord::Promise
.
Source code GitHub
# File activerecord/lib/active_record/relation/calculations.rb, line 182
def async_sum(identity_or_column = nil)
async.sum(identity_or_column)
end
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
Source code GitHub
# File activerecord/lib/active_record/relation/calculations.rb, line 116
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
Source code 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 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.
Source code 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
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
Source code GitHub
# File activerecord/lib/active_record/relation/calculations.rb, line 367
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
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
Source code GitHub
# File activerecord/lib/active_record/relation/calculations.rb, line 146
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
Source code GitHub
# File activerecord/lib/active_record/relation/calculations.rb, line 131
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' ]
Source code GitHub
# File activerecord/lib/active_record/relation/calculations.rb, line 348
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
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, people.id FROM comments INNER JOIN people on comments.person_id = people.id
# => [[1, 2], [2, 2]]
Person.pluck(Arel.sql('DATEDIFF(updated_at, created_at)'))
# SELECT DATEDIFF(updated_at, created_at) FROM people
# => ['0', '27761', '173']
See also ids
.
Source code GitHub
# File activerecord/lib/active_record/relation/calculations.rb, line 287
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))
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(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
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.
Source code 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