2

I have a comment meta field that displays a rating for each comment, I was wondering if there is any way I can query the database to get the sum total of these ratings for each post.

I have included the following code in my single.php, but it obviously gives the total from all the comments. Is there anyway I can alter it to include only comments from the current post.

// set the meta_key to the appropriate custom field meta key
$meta_key = 'rating_total';
$ratingtotals = $wpdb->get_var( $wpdb->prepare( 
    "
        SELECT sum(meta_value) 
        FROM $wpdb->commentmeta 
        WHERE meta_key = %s
    ", 
    $meta_key
) );
  • thanks toscho for sorting my code formatting, didn't realise I could edit the question once posted. – tonykhan Jan 9 '15 at 19:44
1

You can join to the comments table and use get_the_ID():

// set the meta_key to the appropriate custom field meta key
$meta_key = 'rating_total';
$ratingtotals = $wpdb->get_var( $wpdb->prepare( 
    "
        SELECT sum(cm.meta_value) 
        FROM $wpdb->commentmeta AS cm
        JOIN $wpdb->comments AS c ON (c.comment_ID = cm.comment_id)
        WHERE cm.meta_key = %s AND c.comment_post_ID = %d
    ", 
    $meta_key, get_the_ID()
) );
  • Thanks bonger that did the trick, was going round in circles trying to figure this out. – tonykhan Jan 9 '15 at 19:41

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.