Order database results by bayesian rating
- by One Trick Pony
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?
