Using Ruby on Rails is all about what people find to be common uses. These items then become part of the core system. An example of this is counter_cache. For more information on this, I suggest checking out the Railscast on it here.
A little more of an edge case is if you want a column that rather than counting totals, its calculates an average. To me this is just common enough to require a HOWTO and not common enough to be in the core rails system. Hence here is my HOWTO.
Context: Let’s use an example similar to Yelp where there is a business and each business gets reviewed. The review count is handled by the counter_cache so that column already exists in our database.
| reviews_count | INT(11) | YES | | 0 | |
The trick here is that every review also has a rating of the business being reviewed. So we are going to use this rating column from the reviews table to calculate the average rating of a business.
First thing we need to do is create the migration:
beacon:test elubow$ script/generate migration add_average_rating_to_business
exists db/migrate
create db/migrate/20090811135219_add_average_rating_to_business.rb
Edit the migration file to look like this (Note: I know the precision here is 2 when the idea is to not go greater than 5, but MySQL requires precision to be >= scale):
class AddAverageRatingToBusiness < ActiveRecord::Migration
def self.up
add_column :businesses, :average_rating, :decimal, :precision => 4, :scale => 2
end
def self.down
remove_column :businesses, :average_rating
end
end
beacon:site elubow$ rake db:migrate
(in /Users/elubow/Sites/site)
== AddAverageRatingToBusiness: migrating =====================================
-- add_column(:businesses, :average_rating, :decimal, {:scale=>4, :precision=>2})
-> 0.7018s
== AddAverageRatingToBusiness: migrated (0.7021s) ============================
Now we are going to use the after_save callback hook. We are using after_save because we want the current change to be entered into the database before we make any calculations. In our reviews model, we are going to the following lines:
after_create :update_business_average_rating
after_save :update_business_average_rating
after_destroy :update_business_average_rating
def update_business_average_rating
average_rating = Review.average(:rating, :conditions => [ 'business_id = ?', business_id ] )
business = Business.find(:first, business_id)
business.average_rating = average_rating
business.save
end
Let’s walk through this line by line (Note: The business ID and actual ratings will be different based on your data). First, we grab the average rating from every review of that business. What I would like to note is that by default since it is a callback, the review just added is automagically passed into the method. That is how we are able to access the business_id both to find the average of all the reviews of that business, but to also find the business record to update the average_rating attribute.
The method above causes the following SQL to execute:
SELECT avg(`reviews`.rating) AS avg_rating FROM `reviews` WHERE (business_id = 1)
The next line finds the record of the business and updates it with the newly calculated average rating. The resulting executed SQL will be this:
UPDATE `businesses` SET `average_rating` = 3.0, `updated_at` = '2009-08-13 22:08:55' WHERE `id` = 1
Now that we’re done adding the column and calculating its value, don’t forget to add it to your view. A simple display should do just fine.
< %=h @business.average_rating %>
