What's the best way to rank models in rails

Let's say I have Posts and Comments for a blog app using Rails and MySQL.

I want to rank Posts by nu开发者_如何学Gomber of comments. (Note: not just sort, but get their actual 1st,2nd,3rd rank).

Also, given a post, I want to be able to get it's rank without loading all Posts into Rails and searching through them.

e.g. "This post is ranked #372 by number of comments"

Finally, if two posts have the same number of comments, they should have the same rank - so ties are ok.

I've seem some clever solutions to this in MySQL, such as this post: How do I Handle Ties When Ranking Results in MySQL?

I'm wondering if there is a simpler solution by normalizing some of the data in an additional field on the posts table.

Has anyone seen a good approach for this?

---

Thinking more about this, I think there are few different ways you can go about adding this functionality.

You can either add Redis to the mix and use its sorted set functionality ala the top score example in this post, http://jimneath.org/2011/03/24/using-redis-with-ruby-on-rails.html

This would probably be the best design, in my opinion. You divorce the analysis part from the data gathering part and you use redis what it's best at.

Another way is to create a PostRanking model that records the post_id and the comment count. You can put a default scope on this to sort by number of comments in descending order and query it easily.

---

This problem looks identical to the example given for the :counter_cache association option.

Take a look.