How to add/update post meta to use in query?

Question

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.

Answer 1

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.