Module: ActiveRecord::Calculations

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

Overview

Active Record Calculations

Defined Under Namespace

Classes: ColumnAliasTracker

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



110
111
112
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 110

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



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

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

#async_ids ⇒ Object

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



362
363
364
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 362

def async_ids
  async.ids
end

#async_maximum(column_name) ⇒ Object

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



138
139
140
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 138

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



124
125
126
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 124

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



319
320
321
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 319

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



291
292
293
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 291

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



156
157
158
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 156

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



105
106
107
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 105

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



191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 191

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 = [ 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", "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.



84
85
86
87
88
89
90
91
92
93
94
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 84

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

#ids ⇒ Object

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(:companies).ids # SELECT people.id FROM people INNER JOIN companies ON companies.id = people.company_id



327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 327

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
      klass.connection.select_all(relation, "#{klass.name} Ids", async: @async)
    end
  end

  result.then { |result| type_cast_pluck_values(result, columns) }
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



133
134
135
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 133

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



119
120
121
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 119

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



309
310
311
312
313
314
315
316
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 309

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.



257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 257

def pluck(*column_names)
  return [] if @none

  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.flatten)
    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(initial_value_or_column = 0, &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



147
148
149
150
151
152
153
 
# File 'activerecord/lib/active_record/relation/calculations.rb', line 147

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