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

Question

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

sh.dehnavi

2018年12月11日 01:33

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.

Topic rating comment-meta post-meta Wordpress

Category Web

Sally CJ · Accepted Answer · 2018年12月11日 01:33

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:

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;

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.

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

UPDATE

UPDATE #2

UPDATE #3

About