How can I display random blog posts by preferring high-rated posts?

Question

Say I have a blog post table that has a rating field that indicates the quality of the post. What is the most efficient way to randomly search for a message with a higher probability of returning a high-ranking message?

I will implement this in PHP, MySQL and possibly Lucene.

Answer 1

You can use weighted random ordering, for example:

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

+1 to allow posts with 0 points. Depending on the average rating of your post, you may need to multiply the score by another constant factor (for example, 0.5*score+1).

, , LOG(score) SQRT(score).

Answer 2

, RAND() rating :

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

, , SQRT:

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

Answer 3

SQL COMMAND:

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

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

Details for: ORDER BY