Adding An Average Column To A Model

14
Aug

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 %>

Leave a Reply