2

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!

2
  • And please: Don't forget to vote and later accept an answer as solution.
    – kaiser
    May 9, 2011 at 13:57
  • Did you able to get a solution?
    – Sisir
    Dec 29, 2011 at 20:52

5 Answers 5

4

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

    }
}
3
  • Probably you should do the inverse on comment deletion, if that apply to your case.
    – MZAweb
    May 9, 2011 at 15:22
  • Probably faster than my solution. +1 :)
    – kaiser
    May 9, 2011 at 16:07
  • This should be accepted - its much more efficient doing the calculations only when comments are added/removed, rather than every time a particular page loads
    – rmorse
    May 19, 2014 at 16:15
3

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

1
  • You may want to sanitize $comment->comment_post_ID before sending it to the DB.
    – kaiser
    Jan 13, 2015 at 6:37
1

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!

1
  • Thanks alot! Somehow it stopped working, WP DEBUG throws an error in the backend saying " WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near >commentmeta.meta_value FROM $wpdb->commentmeta at line 1] 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 "
    – okiedokey
    Dec 4, 2014 at 8:34
0

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;
}
}
1
  • Note that the function is not tested. If you try it and it fails, please write a detailed description in here. If you can solve it yourself, please edit my Q with the working code, so others searching for a solution will also profit from the A.
    – kaiser
    May 9, 2011 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.