CODEWITHSUNDEEP
ruby-on-railsactiverecord
humble_coder
humble_coder

Reputation: 2787

(Rails, ActiveRecord) How do I use ActiveRecord calculations while still retrieving field names?

Is there any way to use ActiveRecord calculations while also acquiring relevant field names? For example, I'd like to find the average age of all people with the various last names. Basically I'd like to use: 

   Person.average(:age, :group_by => "last_name")

... as a substitute for

   "Select last_name, AVG(age) FROM People GROUP BY last_name"

Any ideas?

Upvotes: 0

Views: 1545

Answers (3)

Shadwell
Shadwell

Reputation: 34784

To return any attribute on the class you're averaging against you're probably better off using the standard find(:all, ...) method with a custom :select option.

So your example would be:

groups_of_people = Person.find(:all, 
  :select => 'avg(age) as avg_age, last_name', 
  :group => 'last_name')

They aren't really Person instances then (although they will be return as such) but you can do:

groups_of_people.each do |g|
  g.last_name
  g.avg_age
end

Upvotes: 1

Simone Carletti
Simone Carletti

Reputation: 176552

Person.average(:age, :select => "last_name", :group => "last_name")

It returns an hash where the key is the value for_last name, the value is the result of the average calculation.

Upvotes: 0

ealdent
ealdent

Reputation: 3747

This works pretty much exactly as you have described (though I think you'd use :group instead of :group_by) for single fields. The return is an OrderedHash, so the keys will be the last names and the values will be the average age for that last name.

Since any field that you return has to be included in the group, if you want to add additional fields, you can try doing something a little hackish. In Postgres, for example, you can concatenate the additional fields like so:

Person.average(:age, :group => "first_name || ',' || last_name")

The keys in the return would be the comma separated first and last names. If you're wanting ActiveRecord::Base objects returned, you have to resort to "SELECT ..." style queries.

Upvotes: 1

Related Questions