CODEWITHSUNDEEP
phpmysqldatabasesearch
whamsicore
whamsicore

Reputation: 8700

How do I display random blog posts, favoring posts with high ratings?

Let's say I have a Blog posts table that has a rating field which indicates the quality of the post. What is the most efficient way to randomly find a post, with a higher chance of returning a highly ranked post?

I will be implementing this in PHP, MySQL, and possibly Lucene.

Upvotes: 3

Views: 176

Answers (3)

nico
nico

Reputation: 51640

You could use a "weighted random" ordering, such as:

SELECT title, body FROM posts ORDER BY (score+1) * RAND() DESC LIMIT 5

The +1 is there to allow posts with 0 score to be selected. Depending on the average score of your post you may have to multiply the score by another constant factor (e.g. 0.5*score+1).

Depending on the distribution of your scores you may want to transform your score, for instance with LOG(score) or SQRT(score).

Upvotes: 2

lonesomeday
lonesomeday

Reputation: 237845

An easy solution would be to include calls to RAND() and to the rating column and multiplying them together:

SELECT title, content FROM blog_posts ORDER BY (rating + 1) * RAND() DESC LIMIT 1;

If you found this gave too much precedence to items with a high rating, you could use SQRT:

SELECT title, content FROM blog_posts ORDER BY SQRT(rating + 1) * RAND() DESC LIMIT 1;

Upvotes: 3

Eray
Eray

Reputation: 7128

SQL COMMAND : 

SELECT * FROM posts WHERE id = '$randomly_generated_ids' ORDER BY ratings ASC

for example $randomly_generated_ids can be "13,10,5,20"

Details for : ORDER BY

Upvotes: 0

Related Questions