1

I have a Posts table with a lot of posts submitted at various times, and every post has a rating with it. For question's sake let's say I have all the required details in the Posts table itself (i.e.contents, ratings, created_at etc are in the Posts table).

I need an algorithm to sort the Posts intelligently based on their ratings and how recently they've been posted. I'm using MySQL backend, so a query would be appropriate.

Snicksie
  • 1,987
  • 17
  • 27
shahalpk
  • 3,383
  • 7
  • 36
  • 55

3 Answers3

5

Asking Google really isn't that hard. the first hit seems to be really good, the second one even is a topic on SO about this, leading to a great article about Bayesian rating which seems to be what you're looking for.

The rest is simple maths - by the Bayesian rating you can easily decide which posts are the best. to take the time into account, simply divide the rating by the minutes since the post was created (for example) and the result is a good rating that take the creation-time into account.

Community
  • 1
  • 1
oezi
  • 51,017
  • 10
  • 98
  • 115
  • @oezi: One doubt, so should i update the score of the posts to the database( say every 10 mins).or how should i implement it? – shahalpk Sep 23 '11 at 10:59
3

If I understand you could try:

SELECT * FROM Posts
ORDER BY ratings DESC, created_at

With this you get higher rating posts on the top and, if rating is the same, the older post comes first.

Marco
  • 56,740
  • 14
  • 129
  • 152
  • no... tat would sort first by ratings, then if there are same rating among multiple rows it would sort by created_at. i need something more logical like sorting based on a product of (recent'ness and rating). – shahalpk Sep 23 '11 at 10:29
  • @Shahal Tharique: _tat would sort first by ratings, then if there are same rating among multiple rows it would sort by created_at_: exactly what I told you!! :) – Marco Sep 23 '11 at 10:33
  • @Shahal Tharique: oezi solution seems the one for you with his first hit. – Marco Sep 23 '11 at 10:35
0

This query gives you highest ratings first, then created_at: 

SELECT *
FROM yourposttable
ORDER BY ratings DESC, 
     created_at DESC;

Update

Your comments suggest you want some kind of weighted algorithm. Follow Oezis links above, they lead to some good pages covering that topic.

Bjoern
  • 15,934
  • 4
  • 43
  • 48