Running a function on comment status change

by

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

2 Answers
2

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

Leave a Comment