Module: ActiveRecord::Calculations

Included in:
Relation
Defined in:
activerecord/lib/active_record/relation/calculations.rb

Instance Method Summary collapse

Instance Method Details

#average(column_name, options = {}) ⇒ Object

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

41
42
43
44
45
# File 'activerecord/lib/active_record/relation/calculations.rb', line 41

def average(column_name, options = {})
  # TODO: Remove options argument as soon we remove support to
  # activerecord-deprecated_finders.
  calculate(:average, column_name, options)
end

#calculate(operation, column_name, options = {}) ⇒ Object

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")

109
110
111
112
113
114
115
116
117
118
119
120
121
# File 'activerecord/lib/active_record/relation/calculations.rb', line 109

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(column_name = nil, options = {}) ⇒ Object

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 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 databsae is thrown.


30
31
32
33
34
35
# File 'activerecord/lib/active_record/relation/calculations.rb', line 30

def count(column_name = nil, options = {})
  # TODO: Remove options argument as soon we remove support to
  # activerecord-deprecated_finders.
  column_name, options = nil, column_name if column_name.is_a?(Hash)
  calculate(:count, column_name, options)
end

#idsObject

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

195
196
197
# File 'activerecord/lib/active_record/relation/calculations.rb', line 195

def ids
  pluck primary_key
end

#maximum(column_name, options = {}) ⇒ Object

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

63
64
65
66
67
# File 'activerecord/lib/active_record/relation/calculations.rb', line 63

def maximum(column_name, options = {})
  # TODO: Remove options argument as soon we remove support to
  # activerecord-deprecated_finders.
  calculate(:maximum, column_name, options)
end

#minimum(column_name, options = {}) ⇒ Object

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

52
53
54
55
56
# File 'activerecord/lib/active_record/relation/calculations.rb', line 52

def minimum(column_name, options = {})
  # TODO: Remove options argument as soon we remove support to
  # activerecord-deprecated_finders.
  calculate(:minimum, column_name, options)
end

#pluck(*column_names) ⇒ Object

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

156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
# File 'activerecord/lib/active_record/relation/calculations.rb', line 156

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, bind_values)
    columns = result.columns.map do |key|
      klass.column_types.fetch(key) {
        result.column_types.fetch(key) { result.identity_type }
      }
    end

    result = result.rows.map do |values|
      values = result.columns.zip(values).map do |column_name, value|
        single_attr_hash = { column_name => value }
        klass.initialize_attributes(single_attr_hash).values.first
      end

      columns.zip(values).map { |column, value| column.type_cast value }
    end
    columns.one? ? result.map!(&:first) : result
  end
end

#sum(*args) ⇒ Object

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

74
75
76
# File 'activerecord/lib/active_record/relation/calculations.rb', line 74

def sum(*args)
  calculate(:sum, *args)
end