Module: ActiveRecord::Calculations

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

Instance Method Summary collapse

Instance Method Details

#async_average(column_name) ⇒ Object

Same as #average but perform the query asynchronously and returns an ActiveRecord::Promise


69
70
71
# File 'activerecord/lib/active_record/relation/calculations.rb', line 69

def async_average(column_name)
  async.average(column_name)
end

#async_count(column_name = nil) ⇒ Object

Same as #count but perform the query asynchronously and returns an ActiveRecord::Promise


56
57
58
# File 'activerecord/lib/active_record/relation/calculations.rb', line 56

def async_count(column_name = nil)
  async.count(column_name)
end

#async_maximum(column_name) ⇒ Object

Same as #maximum but perform the query asynchronously and returns an ActiveRecord::Promise


97
98
99
# File 'activerecord/lib/active_record/relation/calculations.rb', line 97

def async_maximum(column_name)
  async.maximum(column_name)
end

#async_minimum(column_name) ⇒ Object

Same as #minimum but perform the query asynchronously and returns an ActiveRecord::Promise


83
84
85
# File 'activerecord/lib/active_record/relation/calculations.rb', line 83

def async_minimum(column_name)
  async.minimum(column_name)
end

#async_pick(*column_names) ⇒ Object

Same as #pick but perform the query asynchronously and returns an ActiveRecord::Promise


276
277
278
# File 'activerecord/lib/active_record/relation/calculations.rb', line 276

def async_pick(*column_names)
  async.pick(*column_names)
end

#async_pluck(*column_names) ⇒ Object

Same as #pluck but perform the query asynchronously and returns an ActiveRecord::Promise


248
249
250
# File 'activerecord/lib/active_record/relation/calculations.rb', line 248

def async_pluck(*column_names)
  async.pluck(*column_names)
end

#async_sum(identity_or_column = nil) ⇒ Object

Same as #sum but perform the query asynchronously and returns an ActiveRecord::Promise


128
129
130
# File 'activerecord/lib/active_record/relation/calculations.rb', line 128

def async_sum(identity_or_column = nil)
  async.sum(identity_or_column)
end

#average(column_name) ⇒ 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

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

def average(column_name)
  calculate(:average, column_name)
end

#calculate(operation, column_name) ⇒ Object

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
    

163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
# File 'activerecord/lib/active_record/relation/calculations.rb', line 163

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 = [] if group_values.empty?
    end

    relation.calculate(operation, column_name)
  else
    perform_calculation(operation, column_name)
  end
end

#count(column_name = nil) ⇒ 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 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.


43
44
45
46
47
48
49
50
51
52
53
# File 'activerecord/lib/active_record/relation/calculations.rb', line 43

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

#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

284
285
286
# File 'activerecord/lib/active_record/relation/calculations.rb', line 284

def ids
  pluck primary_key
end

#maximum(column_name) ⇒ 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

92
93
94
# File 'activerecord/lib/active_record/relation/calculations.rb', line 92

def maximum(column_name)
  calculate(:maximum, column_name)
end

#minimum(column_name) ⇒ 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

78
79
80
# File 'activerecord/lib/active_record/relation/calculations.rb', line 78

def minimum(column_name)
  calculate(:minimum, column_name)
end

#pick(*column_names) ⇒ Object

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', '[email protected]' ]

266
267
268
269
270
271
272
273
# File 'activerecord/lib/active_record/relation/calculations.rb', line 266

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) ⇒ Object

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]

Person.pluck(Arel.sql('DATEDIFF(updated_at, created_at)'))
# SELECT DATEDIFF(updated_at, created_at) FROM people
# => ['0', '27761', '173']

See also #ids.


216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
# File 'activerecord/lib/active_record/relation/calculations.rb', line 216

def pluck(*column_names)
  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
    klass.disallow_raw_sql!(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
        klass.connection.select_all(relation.arel, "#{klass.name} Pluck", async: @async)
      end
    end
    result.then do |result|
      type_cast_pluck_values(result, columns)
    end
  end
end

#sum(identity_or_column = nil, &block) ⇒ 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

106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
# File 'activerecord/lib/active_record/relation/calculations.rb', line 106

def sum(identity_or_column = nil, &block)
  if block_given?
    values = map(&block)
    if identity_or_column.nil? && (values.first.is_a?(Numeric) || values.first(1) == [])
      identity_or_column = 0
    end

    if identity_or_column.nil?
      ActiveSupport::Deprecation.warn(<<-MSG.squish)
        Rails 7.0 has deprecated Enumerable.sum in favor of Ruby's native implementation available since 2.4.
        Sum of non-numeric elements requires an initial argument.
      MSG
      values.inject(:+) || 0
    else
      values.sum(identity_or_column)
    end
  else
    calculate(:sum, identity_or_column)
  end
end