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

4 Views

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.

Read More

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!

Post Views: 4

Related posts

Leave a Reply

5 comments

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

    }
}

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

  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

  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!

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