Rails: calculated column caching

Sometimes you’re working on a Rails project and you think: “hey! This should be easy!”. Well, most of the time it is. I’m working on a project that allows people to rate objects (what they really are doesn’t matter at all).

I’m using the acts_as_rateable plugin which creates an extra database table containing all ratings. I also have a table with my objects. Using the plugin I’m now able to do the following:

obj = Object.find(:first)
obj.add_rating Rating.new(:rating => 4)
obj.add_rating Rating.new(:rating => 5)
obj.rating
=> 4.5

This works all perfectly, until you want to sort objects by rating. You could construct a huge SQL query to join the two tables, but that’s not really efficient, especially when your database gets bigger.

The solution is very easy and even more elegant. Use a cache! For this, you’ll first have to add a new field to the objects table. Do this in a migration:

add_column :objects, :rating_cache, :float

Now, in the Object model, add the following method:

def rate_with(rating)
  add_rating(rating)
  update_attribute('rating_cache', self.rating)
end

You’ll need to change your controller from using #add_rating to #rate_with. The syntax is exactly the same. Now, when you add a rating, we also store the average rating in the rating_cache column.

To get back to the sorting problem, you can now use the rating_cache column to sort Objects.

Object.find(:all, :order => 'rating_cache DESC')

Of course, you can use this trick on all sorts of relations. Have fun with it.

Continue reading...

Want to keep reading and learn more? I've got you
covered with a hand picked selection of awesome books.

See Ariejan's book recommendations »