0

I found a rating system that users can rate the post when commenting. Every thing is good but I want to use the average rating of posts to sort them to users based on average rating (in fact be able to query).
I don't know how I can do that???
The rating_key and rating_value will be saved in wp_commentmeta table but I want to save the average rating value of every post in wp_postmeta to be able to query posts based on it.
I can get average rating of every post but I do not how to save and update it in wp_postmeta as a special key (like avg_post):

<?php
$results = $wpdb->get_results("SELECT meta_value FROM wp_commentmeta WHERE meta_key = 'rating' ");
foreach($results as $result){
    $rate = $result->meta_value;
    $sum +=$rate;
}
$res = $sum/count($results);
$res = number_format((float)$res,1,'.','');
?>

.

Update:

I created the new field in my custom comment template so:

add_action('comment_form_logged_in_after','additional_fields');
add_action('comment_form_after_fields','additional_fields');
function additional_fields(){
?>
<p class="comment-form-rating">
    <label for="rating">Rating</label>
    <span class="starRting">
    <?php
        for($i=5;$i>=1;$i--)
            echo '<input id="rating'.$i.'" type="radio" name="rating" value="'.$i.'"><label for="rating'.$i.'"></label>';
    ?>
    </span>
</p>

Then I saved the rating values in comment_meta :

function save_comment_meta_phone($comment_id){
    if(!empty($_POST['rating']))
        $rating = sanitize_text_field($_POST['rating']);
        add_comment_meta($comment_id,'rating',$rating);
}
add_action('comment_post','save_comment_meta_phone');

But I need a function to calculate the average rating of every post and save it in post_meta via a key.

  • You can save that meta (avg_post) when the rating meta is saved. But where's the code which saves the rating meta? – Sally CJ Dec 9 '18 at 11:22
  • @SallyCJ i added a field to the comment for rating but the rating meta will be save for every comment in wp_commentmeta. but i want to add average rating as a meta to wp_postmeta for every post to be able to query. did you get what i mean? – sh.dehnavi Dec 9 '18 at 11:31
  • @SallyCJ i updated the question. thanks for helping – sh.dehnavi Dec 9 '18 at 11:41
  • Ok thanks for the update. And I did get what you mean - you want to save the average rating whenever a comment is rated. Am I correct? – Sally CJ Dec 9 '18 at 11:49
  • @SallyCJ yes. i want to save the average rating (as a value) in wp_postmeta table (data base) for every post with a special key. because i want to display my posts based on the average rating for my users to know which one in better. i want to use that meta_key in query to display posts. OK? – sh.dehnavi Dec 9 '18 at 11:57
0

You can use this function, which you'd add to the theme's functions.php file, to update the average post rating which is saved in a private meta named _avg_rating (but you can of course rename it) — private meta has their name starting with an underscore (_):

function update_post_avg_rating( $comment_id, $post_id = 0 ) {
    if ( ! $post_id ) {
        if ( ! $comment = get_comment( $comment_id ) ) {
            return false;
        }

        $post_id = $comment->comment_post_ID;
    }

    // Retrieve the ID of all rating comments in post $post_id.
    $ids = get_comments( [
        'meta_key' => 'rating',
        'post_id'  => $post_id,
        'fields'   => 'ids',
        'status'   => 'all',
    ] );

    // Calculate the average rating (using a custom MySQL query).
    $avg = 0;
    if ( ! empty( $ids ) ) {
        global $wpdb;

        $avg = $wpdb->get_var( "
            SELECT AVG(meta_value + 0) FROM {$wpdb->prefix}commentmeta
            WHERE meta_key = 'rating'
                AND comment_id IN (" . implode( ',', $ids ) . ")
        " );
    }

    // Update the average rating.
    update_post_meta( $post_id, '_avg_rating', $avg );
}

Then call the function after you saved the post rating, like so:

function save_comment_meta_phone( $comment_id ) {
    if ( ! empty( $_POST['rating'] ) ) {
        $rating = sanitize_text_field( $_POST['rating'] );

        // Save the post rating.
        add_comment_meta( $comment_id, 'rating', $rating );

        // Then update the average rating.
        update_post_avg_rating( $comment_id );
    }
}

Btw, you may want to rename the save_comment_meta_phone() function to save_comment_meta_rating?.. (just a suggestion)

And in the update_post_avg_rating() code, in the get_comments() call, you can set the status to approve if you want to include only approved post ratings.

UPDATE

Removed, but you can always view the answer's revisions.

UPDATE #2

Removed, but you can always view the answer's revisions.

UPDATE #3

Hopefully these help you in using the _avg_rating meta:

  1. To show the average rating of an individual post, you can use get_post_meta() like so:

    $avg_rating = get_post_meta( get_the_ID(), '_avg_rating', true );
    $avg_rating = $avg_rating ? number_format_i18n( $avg_rating, 1 ) : 0;
    echo $avg_rating;
    
  2. To sort posts by the _avg_rating meta, while still including posts that do not have the _avg_rating meta, you can use keyed meta_query like so:

    $q = new WP_Query( array(
        'post_type'  => 'post',
        'meta_query' => array(
            'relation'          => 'OR',
            'avg_rating_clause' => array(
                'key'     => '_avg_rating',
                'compare' => 'EXISTS',
                'type'    => 'DECIMAL',
            ),
            'avg_rating_clause2' => array(
                'key'     => '_avg_rating',
                'compare' => 'NOT EXISTS',
                'type'    => 'DECIMAL',
            ),
        ),
        'orderby'    => array(
            'avg_rating_clause2' => 'DESC',
            'date'               => 'DESC',
        ),
        //...
    ) );
    
    // Sample for displaying the posts.
    if ( $q->have_posts() ) {
        while ( $q->have_posts() ) {
            $q->the_post();
    
            $avg_rating = get_post_meta( get_the_ID(), '_avg_rating', true );
            $avg_rating = $avg_rating ? number_format_i18n( $avg_rating, 1 ) : 0;
    
            the_title( '<h2>', ' (Avg. Rating: ' . $avg_rating . ')</h2>' );
        }
    }
    

    See here for more details on the meta_query.

  • my friend, thanks but we have two issue: 1- i want to display the number of ratings for every post, that is why, i tried to use that simple SELECT . see this link [link] (s8.picofile.com/file/8345296742/0.png) but that select all all of rating value for all post. how can select the rating for every post id? (please see my first code box) – sh.dehnavi Dec 10 '18 at 9:20
  • and another issue is: when i use query based on our _avg_rating key to display and sort the posts, the query does not display those posts that have not rating. did you get what i mean? i use this query $args = array( 'post_type' => 'post', 'meta_key' => '_avg_rating', 'orderby' => 'meta_value_num', 'order' => 'DSC', ); – sh.dehnavi Dec 10 '18 at 9:24
  • have you got what i mean? – sh.dehnavi Dec 10 '18 at 10:49
  • The update_post_avg_rating() is indeed calculating the average rating of a post, and that the average rating value is saved in a custom field named _avg_rating. So I believe that has already answered the question - "I need a function to calculate the average rating of every post and save it in post_meta via a key". – Sally CJ Dec 10 '18 at 11:40
  • yes my friend. you did that. thanks a lot and a lot. thanks over and over but if you can, tell me how to display and sort posts based on the _avg_rating and displaying even those posts dont have rating? (still nobody rate them). – sh.dehnavi Dec 10 '18 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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