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.
1 | | 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:
1 2 3 | 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):
1 2 3 4 5 6 7 8 9 | 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 |
1 2 3 4 5 6 | 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:
1 2 3 4 5 6 7 8 9 10 | 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:
1 | 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:
1 | 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.
1 | < %=h @business.average_rating %> |
