Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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:

wp_myrp_ratings table

Is it possible for me to use the ratings.weighted value as the orderby argument in wp_query?

share|improve this question
    
simple answer - no but you can use "posts_orderby" filter instead –  Jesse Apr 6 '13 at 12:55
    
Thanks Jesse. Is this what I'm after? codex.wordpress.org/Plugin_API/Filter_Reference/posts_orderby I'm really not sure how to adapt that example to the myreviewplugin sql above? –  Chris Apr 6 '13 at 13:21
    
haha, Chris, I'm not good at SQL either so I didn't post it as a answer... Sometimes I'll simply use $wpdb->query(SQL) instead of new wp_query(argument) if the SQL is complicated like yours. –  Jesse Apr 6 '13 at 23:20
    
I've got quite a lot of code in wp_query already so the thought of moving to wpdb scares me a little. I'm wondering if I would be better creating a new table containing post_id and average rating and run a cron job to update it every day. Then I could use posts_orderby. It seems as it stands the sql needed to get the average rating is beyond the capabilities of posts_orderby. It doesn't help that the documentation is incomplete. –  Chris Apr 7 '13 at 0:06
    
I think modifying the plugin to store the avg weight in post meta would be simpler than the cron job thing. –  Jesse Apr 7 '13 at 1:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.