1

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

I want to rank Posts by number 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?

Community
  • 1
  • 1
Brian Armstrong
  • 19,707
  • 17
  • 115
  • 144
  • Define rank. You can always cache the number of comments in a counter cache column on the post and search for max comments, but without a more detailed ranking requirement, I can't think of anything else. – Srdjan Pejic Apr 12 '11 at 11:25

2 Answers2

2

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.

Srdjan Pejic
  • 8,152
  • 2
  • 28
  • 24
0

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

Take a look.

phaedryx
  • 2,024
  • 3
  • 16
  • 24