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
This calculates aggregate values in the given column. Methods for count, sum, average, minimum, and maximum have been added as shortcuts.
There are two basic forms of output:
* Single aggregate value: The single value is type cast to Fixnum 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
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")
# File activerecord/lib/active_record/relation/calculations.rb, line 123 def calculate(operation, column_name, options = {}) # TODO: Remove options argument as soon we remove support to # activerecord-deprecated_finders. if column_name.is_a?(Symbol) && attribute_alias?(column_name) column_name = attribute_alias(column_name) end if has_include?(column_name) construct_relation_for_association_calculations.calculate(operation, column_name, options) else perform_calculation(operation, column_name, options) 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 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 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 select
, it will count the
selected columns:
Person.select(:age).count
# => counts the number of different age values
Note: not all valid select
expressions are valid
count
expressions. The specifics differ between databases. In
invalid cases, an error from the database is thrown.
# File activerecord/lib/active_record/relation/calculations.rb, line 38 def count(column_name = nil, options = {}) if options.present? && !ActiveRecord.const_defined?(:DeprecatedFinders) raise ArgumentError, "Relation#count does not support finder options anymore. " "Please build a scope and then call count on it or use the " "activerecord-deprecated_finders gem to enable this functionality." end # TODO: Remove options argument as soon we remove support to # activerecord-deprecated_finders. calculate(:count, column_name, options) 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
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
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
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(:id)
# SELECT people.id FROM people
# => [1, 2, 3]
Person.pluck(:id, :name)
# SELECT people.id, people.name FROM people
# => [[1, 'David'], [2, 'Jeremy'], [3, 'Jose']]
Person.pluck('DISTINCT 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('DATEDIFF(updated_at, created_at)')
# SELECT DATEDIFF(updated_at, created_at) FROM people
# => ['0', '27761', '173']
# File activerecord/lib/active_record/relation/calculations.rb, line 170 def pluck(*column_names) column_names.map! do |column_name| if column_name.is_a?(Symbol) && attribute_alias?(column_name) attribute_alias(column_name) else column_name.to_s end end if has_include?(column_names.first) construct_relation_for_association_calculations.pluck(*column_names) else relation = spawn relation.select_values = column_names.map { |cn| columns_hash.key?(cn) ? arel_table[cn] : cn } result = klass.connection.select_all(relation.arel, nil, relation.arel.bind_values + bind_values) result.cast_values(klass.column_types) 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