Here's the situation-

I've customized the comment submission whereby users can provide a rating, and this gets stored as a meta value in the commentmeta table. I can query to return the average value and display in the post. No problem so far.

What I want to do now is sort posts by this average rating.

My code to get the average rating is:

function average_rating() {
global $wpdb;
$post_id = get_the_ID();
$ratings = $wpdb->get_results("

    SELECT $wpdb->commentmeta.meta_value
    FROM $wpdb->commentmeta
    INNER JOIN $wpdb->comments on $wpdb->comments.comment_id=$wpdb->commentmeta.comment_id
    WHERE $wpdb->commentmeta.meta_key='rating' 
    AND $wpdb->comments.comment_post_id=$post_id 
    AND $wpdb->comments.comment_approved =1

$counter = 0;
$average_rating = 0;    
if ($ratings) {
    foreach ($ratings as $rating) {
        $average_rating = $average_rating + $rating->meta_value;
    //round the average to the nearast 1/2 point
    return (round(($average_rating/$counter)*2,0)/2);  
} else {
    //no ratings
    return 'no rating';

I'm thinking I need to start storing the average value as a post meta field otherwise I'd have to loop through queries within queries with multiple joins, which seems to be a really bad idea. I'm hoping someone with a fresh set of eyes can help me think through architecting this.


I've done exactly something similar. First, use get_comment_meta for getting the per comment rating, you don't need the SQL for it. Then get the average rating (addition of individual comment ratings on a single post / number of comments on this post). Then store this average value as post meta for this post. Use WP_Query custom fields parameters for ordering these posts.

| improve this answer | |
  • that's where I'm getting a bit stuck - how to store the avg value as post meta. since this value changes as more comments are added - perhaps the approving the comment would be the appropriate trigger? – elleeott Dec 8 '11 at 16:13
  • 1
    Exactly. Use comment_unapproved_to_approved (codex.wordpress.org/Plugin_API/Action_Reference/…) action hook to update the final average when the comment is approved (and vice versa as comment_approved_to_unapproved). You'll also have to keep a track when the comment is deleted or trashed (use trashed_comment or deleted_comment), to subtract that comment's rating from the addition and recalculate the score. – Rutwick Gangurde Dec 8 '11 at 16:27
  • 1
    I update the sum of all ratings as post meta, every time a comment is approved/unapproved/trashed/untrashed/deleted using the above hooks. Also store the comment count as another post meta value. Then get these two wherever I want to use them, calculate the average there. – Rutwick Gangurde Dec 8 '11 at 16:29
  • cool. I think that will work. – elleeott Dec 8 '11 at 17:29
  • It will my friend, it definitely will. If stuck, feel free to ask me! – Rutwick Gangurde Dec 8 '11 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.