nice ass nice ass - 3 months ago 20
MySQL Question

Order database results by bayesian rating

I'm not sure this is even possible, but I need a confirmation before doing it the "ugly" way :)

So, the "results" are posts inside a database which are stored like this:


  • the posts table, which contains all the important stuff, like the ID, the title, the content

  • the post meta table, which contains additional post data, like the rating (
    this_rating
    ) and the number of votes (
    this_num_votes
    ). This data is stored in pairs, the table has 3 columns: post ID / key / value. It's basically the WordPress table structure.



What I want is to pull out the highest rated posts, sorted based on this formula:


br = ( (avg_num_votes * avg_rating) + (this_num_votes * this_rating) )
/ (avg_num_votes +
this_num_votes
)


which I stole form here.

avg_num_votes
and
avg_rating
are known variables (they get updated on each vote), so they don't need to be calculated.

Can this be done with a mysql query? Or do I need to get all the posts and do the sorting with PHP?

Answer

Data Stack Exchange Link:

http://data.stackexchange.com/stackoverflow/s/2137/order-database-results-by-bayesian-rating

SELECT id,title,( AVG(this_num_votes) * AVG(this_rating) + this_num_votes * this_rating )
     / ( AVG(this_num_votes) + this_num_votes ) as br
FROM posts
LEFT JOIN (
SELECT DISTINCT post_id,
(SELECT meta_value FROM postmeta WHERE postmeta.post_id = pm.post_id AND meta_key ='this_num_votes') as this_num_votes,
(SELECT meta_value FROM postmeta WHERE postmeta.post_id = pm.post_id AND meta_key ='this_rating') as this_rating
FROM postmeta pm ) as newmeta ON posts.ID = newmeta.post_id
GROUP BY id,title,this_num_votes,this_rating
ORDER BY br DESC
Comments