0

I want to get top rated posts using wp query. The rating value is store in comment meta database table when some one post a comment on wp post or cpt.Please screenshot

enter image description here

EDIT Here is my code

add_action( 'comment_form_top', 'wpcr_change_comment_form_defaults');
function wpcr_change_comment_form_defaults( ) {


    $star1_title = __('Very bad', 'post-rating');
    $star2_title = __('Bad', 'post-rating');
    $star3_title = __('Meh', 'post-rating');
    $star4_title = __('Pretty good', 'post-rating');
    $star5_title = __('Rocks!', 'post-rating');


    echo '<fieldset class="rating">
    <legend>Rating<span class="required">*</span></legend>
    <input type="radio" id="star5" name="rating" value="5" /><label for="star5" title="'.$star5_title.'">5 stars</label>
    <input type="radio" id="star4" name="rating" value="4" /><label for="star4" title="'.$star4_title.'">4 stars</label>
    <input type="radio" id="star3" name="rating" value="3" /><label for="star3" title="'.$star3_title.'">3 stars</label>
    <input type="radio" id="star2" name="rating" value="2" /><label for="star2" title="'.$star2_title.'">2 stars</label>
    <input type="radio" id="star1" name="rating" value="1" /><label for="star1" title="'.$star1_title.'">1 star</label>
    </fieldset>';

}
//////// save comment meta data ////////
add_action( 'comment_post', 'wpcr_save_comment_meta_data' );

function wpcr_save_comment_meta_data( $comment_id ) {
    $rating =  (empty($_POST['rating'])) ? FALSE : $_POST['rating'];
    add_comment_meta( $comment_id, 'rating', $rating );
}
3
  • Can you add the code of how you're handling the stars right now?
    – kero
    Jun 6, 2019 at 15:45
  • 1
    Is that rating field coming from some plugin? Jun 7, 2019 at 8:17
  • No, I am using custom code to add star rating. Jun 8, 2019 at 13:19

1 Answer 1

0

Don't worry. I have found solution for your issue. you can use below code to display 5 top rated posts based on average rating. i have created below function to get top average rated post using sql queries and wp_query functions.

here is updated function for all custom post type.

function top_rated_post_via_comment_of_CPT($post_per_page = 5){
 global $wpdb;

    $results = $wpdb->get_results("SELECT DISTINCT(wp_comments.comment_post_ID), GROUP_CONCAT(wp_comments.comment_iD separator ', ') comment_ids FROM wp_comments JOIN wp_commentmeta ON wp_commentmeta.comment_id = wp_comments.comment_ID GROUP BY wp_comments.comment_post_ID", ARRAY_A);


    foreach($results as $key => $value) {

        $c_post_id = $value['comment_post_ID'];
        $comment_ids = $value['comment_ids'];
        $res = $wpdb->get_results( "SELECT AVG(`meta_value`) as avg_rate FROM wp_commentmeta WHERE `meta_key` = 'rating' AND comment_ID IN ($comment_ids) ORDER BY meta_value" );
       $results[$key]['avg_rate'] = $res[0]->avg_rate;
    }
    # sort value by high rated
    $avg_rate = array_column($results, 'avg_rate');
    array_multisort($avg_rate, SORT_DESC, $results);

    $top_rated = array();
    foreach ($results as $result) {

        if($result['avg_rate'] && $result['comment_ids'] )
        {
            $top_rated[] = $result['comment_post_ID'];
        }
    }

    $args = array(
        'post_type' => array("post","movies"),
        'posts_per_page' => $post_per_page,
        'post__in' => $top_rated,
        'orderby' => 'post__in' 
    );

    $top_rated_posts = new WP_Query( $args );

    // The Loop
    if ( $top_rated_posts->have_posts() ) {
        echo '<ul>';

        while ( $top_rated_posts->have_posts() ) {
            $top_rated_posts->the_post();
            $new_key = array_search(get_the_id(), array_column($results, 'comment_post_ID'));
            echo '<li>Post Name : ' . get_the_title() . ' | Average Rate :'.number_format((float)$results[$new_key]['avg_rate'], 2, '.', '').'</li>';

        }
        echo '</ul>';

        wp_reset_postdata();
    } else {
        // no posts found
    }
}

echo top_rated_post_via_comment_of_CPT(5);

to add Average column in admin post listing dashboard

 add_filter( 'manage_movies_posts_columns', 'set_custom_edit_columns' );
    add_filter( 'manage_posts_columns', 'set_custom_edit_columns' );
    function set_custom_edit_columns($columns) {
        $columns['avg_rate'] = __( 'Average Rate', 'your_text_domain' );
        return $columns;
    }

// Add the data to the custom columns for the book post type:
add_action( 'manage_posts_custom_column' , 'custom_column', 10, 2 );
add_action( 'manage_movies_custom_column' , 'custom_column', 10, 2 );
function custom_column( $column, $post_id ) {
    switch ( $column ) {

        case 'avg_rate' :
            global  $wpdb;
            $results = $wpdb->get_results("SELECT DISTINCT(wp_comments.comment_post_ID), GROUP_CONCAT(wp_comments.comment_iD separator ', ') comment_ids FROM wp_comments JOIN wp_commentmeta ON wp_commentmeta.comment_id = wp_comments.comment_ID GROUP BY wp_comments.comment_post_ID", ARRAY_A);
            foreach($results as $key => $value) {

                $c_post_id = $value['comment_post_ID'];
                $comment_ids = $value['comment_ids'];
                $res = $wpdb->get_results( "SELECT AVG(`meta_value`) as avg_rate FROM wp_commentmeta WHERE `meta_key` = 'rating' AND comment_ID IN ($comment_ids) ORDER BY meta_value" );
               $results[$key]['avg_rate'] = $res[0]->avg_rate;
            }
            $new_key = array_search($post_id, array_column($results, 'comment_post_ID'));
            if($results[$new_key]['avg_rate']){
            echo number_format((float)$results[$new_key]['avg_rate'], 2, '.', '');
            }
            else
            {
                echo "No rating";
            }
            break;
    }
}

i hope this function can solve you query. let me know if this function helps you!

Thank you!

19
  • Thanks. I'll test this code and then contact you soon. Jun 18, 2019 at 9:42
  • okay. let me know if any confusion. Jun 18, 2019 at 10:14
  • Sql query returns an empty array.Average rating is shown as 0.00. Jun 19, 2019 at 8:25
  • i have queries. do you have any rated posts ? is wp_ your table prefix ? if not then you have to change prefix in code to your table prefix. because i have used your comment form and comment meta and develop this function to get top average rated posts. t have tested and it is working. Jun 19, 2019 at 8:45
  • Yes, I have 2 rated posts. And also I change tabel prefix and getting only one rated posts with correct average. Jun 19, 2019 at 9:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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