Rails: calculated column caching
6 Dec, 2007

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)
end
Object.find(:all, :order => 'rating_cache DESC')