how to SELECT for the current post id?

Question

i am trying to get and count the values of a meta key (rating) and then calculate the sum. but the following code get all of values in wp_commentmeta table but i want to get just for the current post. how should i solve that?

$results = $wpdb->get_results("SELECT meta_value FROM {$wpdb->prefix}commentmeta WHERE meta_key = 'rating' ");
foreach($results as $result){
    $rate = $result->meta_value;
    $sum +=$rate;
}
$res = $sum/count($results);
$res = number_format((float)$res,2,'.','');

and how to count the number of 5 star ratings (the special value) for the current post. the code:

$countstar = $wpdb->get_var("SELECT count(meta_value) FROM {$wpdb->prefix}commentmeta WHERE meta_key = 'rating' AND meta_value = '5'");

Answer 1

You should be able to do that by joining data from wp_comments table as this table contains information about which comments belong to which posts

$sql = $wpdb->prepare( "
    SELECT meta_value 
    FROM {$wpdb->prefix}commentmeta 
    INNER JOIN {$wpdb->prefix}comments ON {$wpdb->prefix}commentmeta.comment_id = {$wpdb->prefix}comments.comment_ID 
    WHERE comment_post_ID = %d AND meta_key = 'rating' ", get_the_ID() 
);
$results = $wpdb->get_results( $sql );

Note that MySQL can sum the values for you, you do not need to do that using PHP.

UPDATED:

The number of 5 star ratings for current post you can count with the below query

$sql = $wpdb->prepare( "
    SELECT count(meta_value) 
    FROM {$wpdb->prefix}commentmeta 
    INNER JOIN {$wpdb->prefix}comments ON {$wpdb->prefix}commentmeta.comment_id = {$wpdb->prefix}comments.comment_ID 
    WHERE comment_post_ID = %d AND meta_key = 'rating' AND meta_value = '5' ", get_the_ID() 
);
$wpdb->get_var( $sql );

although since you are iterating over the ratings in foreach loop anyway then inside the loop you can count 5 star ratings without running a second query like this

$rating5 = 0;
foreach($results as $result){
    if( $result->meta_value == '5' ) {
        $rating5++;
    }
    $rate = $result->meta_value;
    $sum +=$rate;
}