CODEWITHSUNDEEP
ruby-on-railsactiverecordruby-on-rails-4
if __name__ is None
if __name__ is None

Reputation: 11533

ActiveRecord get average from group

I am trying to write this query in ActiveRecord 4, but to no avail.

SELECT date, AVG(gain) AS avg_gain FROM counters WHERE ( date > '2014-03-03'  ) GROUP BY date ORDER BY date DESC;

So I scrambled this together:

 Counter.select("date, AVG(gain) as avg_gain").where("date > '2014-03-03'").group(:date).order(date: :desc)
=> #<ActiveRecord::Relation [#<Counter id: nil, date: "2014-04-01">, #<Counter id: nil, date: "2014-03-31">, #<Counter id: nil, date: "2014-03-30">, #<Counter id: nil, date: "2014-03-29">, #<Counter id: nil, date: "2014-03-28">, #<Counter id: nil, date: "2014-03-27">, #<Counter id: nil, date: "2014-03-26">, #<Counter id: nil, date: "2014-03-25">, #<Counter id: nil, date: "2014-03-24">, #<Counter id: nil, date: "2014-03-23">, ...]>

The only trouble is, the result does not contain avg_gain columns. Any ideas?

Upvotes: 1

Views: 2063

Answers (2)

webaholik
webaholik

Reputation: 1815

OP wants to see hash values for the items queried. Here are two options, but you should just use the first, the second is here as more of a warning or option when performance doesn't matter:

I'd highly recommend this option, due to performance reasons:

Counter.
    where("date > '2014-03-03'").
    group(:date).
    order(date: :desc).
    pluck("date, AVG(gain) as avg_gain").
    map { |date, avg_gain| {date: date, avg_gain: avg_gain} }

The solution below looks nice but is about 10x slower. This is because as_json will instantiate a model object for every result:

The Active Model JSON Serializer method, as_json will also return an array of hashes. By default, each result will also include "id"=>nil if you don't use the only: except: option.

Counter.
    select("date, AVG(gain) as avg_gain").
    where("date > '2014-03-03'").
    group(:date).
    order(date: :desc).
    as_json(except: [:id])

Upvotes: 0

vee
vee

Reputation: 38645

When you use select, the selected fields are added to the instances in the returned Relation. The returned result does not include those selected fields in the printed output.

Access these fields just as you would access a column attribute: 

result = Counter.select("date, AVG(gain) as avg_gain").where("date > '2014-03-03'").group(:date).order(date: :desc)
result.first.avg_gain # Prints expected value for the first Counter

Upvotes: 3

Related Questions