ActiveRecord average with `limit` method

tags: [jason] [activerecord]

Taking the Average with a limit method

User.create(spending: 100)
User.create(spending: 200)
User.create(spending: 900)

User.average(:spending) # => 400
User.order(spending: :asc).limit(2).average(:spending) # => 400 'doh, not 150

Workarounds

* Subquery

One work around was to write a subquery, but that turned out to be more sql then desired

def self.average_with_limit
  subquery = order(spending: :asc).limit(2).to_sql
  query = "SELECT AVG(subquery.spending) FROM (#{subquery}) AS subquery"

  response = ActiveRecord::Base.connection.execute(query) #=> An array-like object, full of hashes
  response.first.first.to_i
end

User.average_with_limit #=> 150 yay

* Using where

Adding a where method turned out to be a bit less sql-ish. This also had the advantage of making the interface with the database more robust. ie, if we change database backends, there’s less hard coded sql queries.

User.where('spending < ?', 200).average(:spending) # => 100 yay!

* Getting just that column

Maybe easier to just grab that spend column for large tables

spendings = User.order(spending: :asc).pluck(:spending)

spendings[0, 2].inject(:+) / 2 # => 100 yay!