Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I am making a review system where i need to find update rating average when:

  • A comment gets approved from pending/trash/spam.
  • A comment gets upapprove from approve
  • A admin post reviews (auto approved)
  • A comment gets edited (from backend or frontend custom dashboard when user wants to edit/update/remove his already made review)

Here is the code i've written:

add_action('edit_comment', 'update_business_rating_avg');
add_action('comment_post', 'update_business_rating_avg');
add_action('comment_unapproved_to_approved', 'update_business_rating_avg');
add_action('comment_approved_to_unapproved', 'update_business_rating_avg');
add_action('comment_spam_to_approved', 'update_business_rating_avg');
add_action('comment_approved_to_spam', 'update_business_rating_avg');

    function update_business_rating_avg($comment){
        //fb($comment);
        $post_id = $comment->comment_post_ID;
        $post_type = get_post_type($post_id);
        if('business' == $post_type){
        //fb($post_type);
            $args = array(
                                    'post_id' => $post_id,
                                    'status' => 'approve'
                        );
            $comments = get_comments($args);
            $total_ratings = 0;
            $avg = 0;
            foreach($comments as $comment){
                $total_points += get_comment_meta($comment->comment_ID, 'rating', true);    
                $total_ratings++;
            }
            if($total_ratings > 0){
                $avg = $total_points/$total_ratings;
                $avg = (float)$avg;
            }
            $avg = roundToNearestFraction($avg,0.5);
            update_post_meta($post_id, 'avg_rating', $avg);
        }
    }

Problem:

  • I am still missing something because when an admin post a review the function not triggered (Its gets auto approved).

  • Check the function update_business_rating_avg() and how I use get_comment_meta() inside foreach. Is it performance killing? Is there a better way to do this? Custom SQL parhaps?

    Appreciate your help, Thanks!

SOLVED: Final Code

Thanks to @Soulseekah to send me to right direction. Here is final code for the solution if someone needs it in future. The SQL is a complex one because i had to JOIN both wp_comments and wp_commentmeta to compare approved comment and comment associated with the current post ID.

add_action('edit_comment', 'update_business_rating_avg');
add_action('comment_post', 'update_business_rating_avg');
add_action('comment_unapproved_to_approved', 'update_business_rating_avg');
add_action('comment_approved_to_unapproved', 'update_business_rating_avg');
add_action('comment_spam_to_approved', 'update_business_rating_avg');
add_action('comment_approved_to_spam', 'update_business_rating_avg');
add_action('comment_approved_to_trash', 'update_business_rating_avg');
add_action('comment_trash_to_approved', 'update_business_rating_avg');

function update_business_rating_avg($comment){
    if ( !is_object( $comment ) ){
        $comment = get_comment( $comment );
    }

    $post_id = $comment->comment_post_ID;
    $post_type = get_post_type($post_id);
    if('business' == $post_type){
        $avg = 0;
        global $wpdb;
        $query = "SELECT AVG(meta_value) FROM $wpdb->commentmeta ";
        $query .= "INNER JOIN $wpdb->comments ON $wpdb->commentmeta.comment_id = $wpdb->comments.comment_ID " ;
        $query .= "WHERE $wpdb->commentmeta.meta_key = 'rating' ";
        $query .= "AND $wpdb->comments.comment_approved = 1 ";
        $query .= "AND $wpdb->comments.comment_post_ID = '$post_id'";

        if( $result = $wpdb->get_var($wpdb->prepare($query)) ){
            $avg = roundToNearestFraction($result, 0.5);
            update_post_meta($post_id, 'avg_rating', $avg);
        }else{
            // report error
            //$wpdb->print_error();
        }
    }
}
share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted
+50

The comment_post hook is called with a $comment_id as the first argument. You can see why your function is failing. Same goes for the edit_comment hook.

Simply add the following at the beginning of your business function:

if ( !is_object( $comment ) )
    $comment = get_comment( $comment );

Do use a custom SQL query for comment metadata retrieval, as right now, with what you have there, you'll end up querying the database for each and every comment. Moreover, I would additionally suggest using the built-in SUM and AVG MySQL functions to avoid the extra loops in PHP.

SELECT SUM(`meta_value`), AVG(`meta_value`)
FROM `{$wpdb->commentmeta}`
WHERE `meta_key` = 'rating'
AND `comment_id` = '$sanitized_comment_id';
share|improve this answer
add comment

I found that having the $result inside the if() statement wasn't working for me.

if( $result = $wpdb->get_var($wpdb->prepare($query)) ){

So if you have the same issue, try this:

$result = $wpdb->get_var($wpdb->prepare($query));
if( $result ){ ... }
share|improve this answer
2  
There is no difference between both statements. –  toscho Aug 9 '12 at 6:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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