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.
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 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.
Removed, but you can always view the answer's revisions.
Removed, but you can always view the answer's revisions.
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.
avg_post) when theratingmeta is saved. But where's the code which saves theratingmeta? – Sally CJ Commented Dec 9, 2018 at 11:22