CODEWITHSUNDEEP
phpmysqlalgorithm
shahalpk
shahalpk

Reputation: 3452

How to find the best posts based on their ratings and the time of posting?

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.

Upvotes: 1

Views: 124

Answers (3)

oezi
oezi

Reputation: 51817

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.

Upvotes: 5

Bjoern
Bjoern

Reputation: 16304

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.

Upvotes: 0

Marco
Marco

Reputation: 57593

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.

Upvotes: 3

Related Questions