Imagine this: you’re building YouTubeCooking.com. You’ve created your main model–a Recipe–along with a supplementary model–a Vote, with a score field, to represent a one-to-five star rating people vote for on a video recipe.
Now, for your front page, you want to list the top five recipes, as measured by their rating. What is their rating? The total of all their votes, divided by the number of votes. So if someone voted 5, 5, and 2 for your Banana Bread, the rating is (5+5+2) / 3 which equals 4.
Being naive, you might think that Rails makes this easy–ordering by a calculated field implied through the object model. After all, the SQL is really simple, right?
SELECT * FROM Recipes LEFT JOIN Votes ON Votes.recipe_id = Recipes.id ORDER BY SUM(Votes.score) / COUNT(Votes.score)
So you make an attempt to introduce a topRated list in your controller, like so:
@topRated = Recipe.find :all, :include => [:votes], :order => "SUM(votes.score) / COUNT(Votes.score)
Nope, doesn’t work–Rails complains about the grouping clause, or something else. So you try again–this time, adding the SQL join manually:
@topRated = Recipe.find :all, :include => [:votes], :join => "LEFT JOIN Votes ON Recipe.id = Votes.recipe_id", :order => "SUM(votes.score) / COUNT(Votes.score)
Nope, doesn’t work–Rails complains again. And again! Contrary to what you might think–and to what I thought–this approach doesn’t end with a workable solution. Maybe it’s because find is not meant to be used with complex SQL cases. The SQL to get this information is very simple (and you should verify it by running it directly on your database to make sure your query is correct), but it’s not easy to use through find.
And there’s a hint that this is true–the API mentions a similar function–find_by_sql. That looks promising. So try that, instead:
@topRated = Recipe.find_by_sql("SELECT Recipe.id, SUM(votes.score) / COUNT(votes.score) AS average FROM Recipe JOIN Votes ON Recipe.id = Votes.recipe_id GROUP BY votes.recipe_id ORDER BY average")
Voila! It works, flawlessly–even on cases where you have recipes that have no votes on them. And you don’t even need to check for divide-by-zero errors. Amazing!
