How to get the average of the values from the comment meta

Question

I used the comment meta to add a simple rating system. The user can post a rate from the comment form from where I added 3 dropdown comment meta.

The rating is working well, reflecting the ratings together with the comment written by the user. My only problem now is this: how can I get the average of all the ratings posted? I need the average to be placed on the post content.

My rating system rates the following:

• Price,
• Packaging,
• Quality.

I want an average for each rate:

• Average Price Rate,
• Average Packaging Rate, and
• Average Quality Rate.

Thank you so much!

Answer 1

If you need to show the averages in the content, you need to pre-calculate them (before showing the comments).

My approach would be having a custom meta in the post with the calculated averages and modify those metas every time a new comment (rating) is saved.

Something like

add_action("comment_post", "wpse16733_updateAVGs");

function wpse16733_updateAVGs($comment_ID, $approved){

    if ($approved){
        $commentdata=get_comment($comment_ID, ARRAY_A); 
        $parent_post=get_post($commentdata['comment_post_ID']);

        (... get your rating, get post meta, calc and save ...)

    }
}

Answer 2

I've got something similar using a custom query to calculate the average on the fly - per Rabino's comment, it would be more efficient to store the result of this function as a meta value, but I'd want it triggered when a comment is approved, rather than when a comment is saved.

here's your function:

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;
            $counter++;
        } 
        //round the average to the nearast 1/2 point
        return (round(($average_rating/$counter)*2,0)/2);  
    } else {
        //no ratings
        return 'no rating';
    }
}

In my context I have a 1-5 rating. no results of the query means no ratings provided.

Drop the following in the loop and you're good to go:

<?php echo average_rating(); ?>

Answer 3

function set_average_rating( $comment_id ) {
    $comment = get_comment( $comment_id );
    global $wpdb;
    $rating = $wpdb->get_var("       
        SELECT AVG(meta_value) AS avg_rating 
        FROM wp_commentmeta
        WHERE meta_key = 'rating'
        AND comment_id IN (
            SELECT comment_id
            FROM wp_comments
            WHERE comment_post_ID = $comment->comment_post_ID
            AND comment_approved = 1
        )
    ");
    update_post_meta( $comment->comment_post_ID, 'avg_rating', round( $rating, 2 ) );  
}
add_action( 'comment_post', 'set_average_rating' );

Similar to PaulIsLoud's answer, but calculates the average directly in the query instead of iterating the results

Answer 4

Here's my version, based on both of the answers above. It runs on wp_set_comment_status changed to approve.

calc_avg_rating() counts comments having a field of rating (if a partuclar comment has no rating, it simply moves on), and when a new comment is approved, it updates the post meta value of avg_rating.

Then, for my template, I simply call get_product_rating, which looks at the post meta field of avg_rating, that way we're not calculating all of this every time the page is loaded.

add_action("wp_set_comment_status", "calc_average_rating");

    function calc_average_rating($comment_ID, $approved) {
        if ($approved = 'approve'){
            $commentdata=get_comment($comment_ID, ARRAY_A); 
            $parent_post=get_post($commentdata['comment_post_ID']);

            global $wpdb;
            $post_id = $parent_post->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;
                    $counter++;
                } 
                //round the average to the nearast 1/2 point
                $rating = (round(($average_rating/$counter)*2,0)/2);  
            } else {
                //no ratings
                $rating = '';
            }
            update_post_meta($post_id, 'avg_rating', $rating);
        }
    }

    function get_product_rating() {
        $post_id = get_the_ID();
        $value = get_post_meta($post_id, 'avg_rating', true);
        return $value;
    }

Hope this helps someone!

Answer 5

This is a solution which will display the average after the last comment. As a workaround you could simply do two have_comments() comments loops and calculate the average rating with the first loop and display the comments with the second loop.

/**
 * You need to place this function inside your comments callback function, so it get's
 * triggered with every displayed comment inside the have_comments() loop.
 */
function wpse16733_get_comment_meta_avrg()
{
$divider = (int) $GLOBALS['wp_query']->comment_count;

// initial static values - these get counted up everytime the function get's triggered
static $price = 0;
static $packaging = 0;
static $quality = 0;
static $current_comment = 0;

$current_comment = (int) $current_comment++;

// receive all comment meta data
$all_meta = get_comment_meta( get_comment_ID(), '' );

// Now get the ratings (it could also be `$avrg_xy = $all_meta->rating` if an object)
$price = (int) $price + (int) $all_meta['price'];
$packaging = (int) $packaging + (int) $all_meta['packaging'];
$quality = (int) $quality + (int) $all_meta['quality'];

// calculate within the last comment
if ( $current_comment == $divider ) 
{
    $average['price'] = $price / $divider;
    $average['packaging'] = $packaging / $divider;
    $average['quality'] = $quality / $divider;
}

// now do stuff with the $average array
foreach ( $average as $rating )
{
    echo 'This is the average rating: '.$rating;
}
}