module ActiveRecord::Calculations
Public Instance Methods
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 56 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
# File lib/active_record/relation/calculations.rb, line 126 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 = [] 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", "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.
# File lib/active_record/relation/calculations.rb, line 40 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
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 219 def ids pluck primary_key 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
# File lib/active_record/relation/calculations.rb, line 74 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
# File lib/active_record/relation/calculations.rb, line 65 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' ]
# File lib/active_record/relation/calculations.rb, line 211 def pick(*column_names) limit(1).pluck(*column_names).first end
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(: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('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 180 def pluck(*column_names) if loaded? && (column_names.map(&:to_s) - @klass.attribute_names - @klass.attribute_aliases.keys).empty? 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) relation = spawn relation.select_values = column_names result = skip_query_cache_if_necessary { klass.connection.select_all(relation.arel, nil) } result.cast_values(klass.attribute_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
# File lib/active_record/relation/calculations.rb, line 83 def sum(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(:sum, column_name) end end
Private Instance Methods
# File lib/active_record/relation/calculations.rb, line 259 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
# File lib/active_record/relation/calculations.rb, line 408 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
Converts the given field to the value that the database adapter returns as a usable column name:
column_alias_for("users.id") # => "users_id" column_alias_for("sum(id)") # => "sum_id" column_alias_for("count(distinct users.id)") # => "count_distinct_users_id" column_alias_for("count(*)") # => "count_all"
# File lib/active_record/relation/calculations.rb, line 375 def column_alias_for(field) column_alias = +field column_alias.gsub!(/\*/, "all") column_alias.gsub!(/\W+/, " ") column_alias.strip! column_alias.gsub!(/ +/, "_") connection.table_alias_for(column_alias) end
# File lib/active_record/relation/calculations.rb, line 255 def distinct_select?(column_name) column_name.is_a?(::String) && /\bDISTINCT[\s(]/i.match?(column_name) end
# File lib/active_record/relation/calculations.rb, line 224 def has_include?(column_name) eager_loading? || (includes_values.present? && column_name && column_name != :all) end
# File lib/active_record/relation/calculations.rb, line 267 def operation_over_aggregate_column(column, operation, distinct) operation == "count" ? column.count(distinct) : column.send(operation) end
# File lib/active_record/relation/calculations.rb, line 228 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
# File lib/active_record/relation/calculations.rb, line 399 def select_for_count if select_values.present? return select_values.first if select_values.one? select_values.join(", ") else :all end end
# File lib/active_record/relation/calculations.rb, line 390 def type_cast_calculated_value(value, type, operation = nil) case operation when "count" then value.to_i when "sum" then type.deserialize(value || 0) when "average" then value&.respond_to?(:to_d) ? value.to_d : value else type.deserialize(value) end end
# File lib/active_record/relation/calculations.rb, line 385 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