Wpdb query for comment meta for current post

Question

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
) );

Answer 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()
) );