I'm using the myreviewplugin to add ratings to posts. I'm now working on the posts loop and customising the query using wp_query.
Everything is working fine, but I need to expand the orderby argument to order by average ratings. The problem is, ratings are not stored in wp_postmeta, but their own table called 'wp_myrp_ratings'.
The plugin contains a 'top rated' widget that takes all the ratings in 'wp_myrp_ratings' (spread across rating categories) and calculates the average rating. Here is the SQL being produced:
SELECT AVG(ratings.weighted) as sort, posts.* FROM `wp_posts` as posts, `wp_myrp_ratings` as ratings,`wp_comments` as com WHERE posts.ID=ratings.post_id AND posts.post_status='publish' AND ratings.outof!=1 AND ratings.outof!='' AND ratings.category_id='1' AND ratings.comment_id!='-1' AND ratings.comment_id=com.comment_ID AND (com.comment_approved=1) GROUP BY `ratings`.`post_id` ORDER BY `sort` DESC, `posts`.`ID`
Here is a look at the wp_myrp_ratings table:
Is it possible for me to use the ratings.weighted value as the orderby argument in wp_query?