Adding An Average Column To A Model

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

  def self.down
    remove_column :businesses, :average_rating
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

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 %>
Posted in Rails. Tags: . No Comments »