Top rated posts Average rating issue

I am trying to show top rated posts, cpt with AVG rating value.I created the custom widget for this. Defaults wp posts data and AVG value is showing correct but a CPT like Movie post not showing correct AVG value. I'm using following code to achieve this. Need help

public function widget( $args, $instance ) {
$title          = apply_filters( 'widget_title', $instance['title'] );
$posttype       = $instance['posttype'];
$postsperpage   = $instance['postsperpage'];
$postorder      = $instance['postorder'];
$avgrt_pos      = $instance['avgrt_pos'];
$image          = $instance[ 'showimage' ] ? 'true' : 'false';
$avgrt_img      = $instance[ 'avgrt_star' ];

// before and after widget arguments are defined by themes
echo $args['before_widget'];
if ( ! empty( $title ) )
echo $args['before_title'] . $title . $args['after_title'];


global $wpdb, $post;

    $results = $wpdb-get_results("SELECT DISTINCT({$wpdb-prefix}comments.comment_post_ID), GROUP_CONCAT({$wpdb-prefix}comments.comment_ID separator ', ') comment_ids FROM {$wpdb-prefix}comments JOIN {$wpdb-prefix}commentmeta ON {$wpdb-prefix}commentmeta.comment_id = {$wpdb-prefix}comments.comment_ID 
     WHERE {$wpdb-prefix}comments.comment_approved = 1 GROUP BY {$wpdb-prefix}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 {$wpdb-prefix}commentmeta WHERE `meta_key` = 'rating' AND `meta_value` != '' AND comment_ID IN ($comment_ids) ORDER BY meta_value" );

          $results[$key]['avg_rate'] = $res[0]-avg_rate;
          //var_dump($res);
    }

    # sort value by high rated
    if($postorder=='DESC'){
        $sortOrder = SORT_DESC;
    }else{
        $sortOrder = SORT_ASC;
    }
    # avg stars
    if($avgrt_img=='avgrt_str1'){
        $avgrt_img = 'star-1';
    }elseif($avgrt_img=='avgrt_str2'){
        $avgrt_img = 'star-2';
    }elseif($avgrt_img=='avgrt_str3'){
        $avgrt_img = 'star-3';
    }elseif($avgrt_img=='avgrt_str4'){
        $avgrt_img = 'star-4';
    }elseif($avgrt_img=='avgrt_str5'){
        $avgrt_img = 'star-5';
    }elseif($avgrt_img=='avgrt_str6'){
        $avgrt_img = 'star-6';
    }else{
        $avgrt_img = 'star-1';
    }



    $avg_rate = array_column($results, 'avg_rate');
    array_multisort($avg_rate, $sortOrder, $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' = $posttype,
      'posts_per_page' = $postsperpage,
      'post__in' = $top_rated,
      'orderby' = 'post__in',
      'post_status' = 'publish', 
   );

  $top_rated_posts = new WP_Query( $args );
    //var_dump($top_rated_posts);
  // The Loop
  if ( $top_rated_posts-have_posts() ) 
  {
     echo 'div class="wpcr_top_rated_container"ul';
     $i=0;
     while ( $top_rated_posts-have_posts() ) 
     {
        $top_rated_posts-the_post();
        $postid= get_the_ID();

            /* grab the url for the full size featured image */
            $featured_img_url = get_the_post_thumbnail_url(get_the_ID(),'thumbnail');
            $get_post_link = get_permalink();

            if($image == 'true'){
                if(!empty($featured_img_url)){
                    $feaimage = 'a href="'.esc_url($get_post_link).'"img src="'.esc_url($featured_img_url).'" alt=""/a';
                }else {
                    $feaimage = '';
                }
            }   
            $topr_desc = get_the_content();
            $words = 15;
            $more = ' […]';
            $excerpt = wp_trim_words( $topr_desc, $words, $more );

            $avgrating = 'div class="wpcr_aggregate"a class="wpcr_inline" title=""span class="wpcr_averageStars_tprt '.$avgrt_img.'" data-wpcravg="'.number_format((float)$results[$i]['avg_rate'], 2, '.', '').'"/span/aspan class="avg-inline"'.number_format((float)$results[$i]['avg_rate'], 2, '.', '').'/span/div';

                    //var_dump($results[$i]['avg_rate']);   

            if($avgrt_pos == 'before'){
                $avg_bef = $avgrating;
            }
            if($avgrt_pos == 'after'){
                $avg_aft = $avgrating;
            }

                $output = 'li';
                $output .= 'div class="left"'.$feaimage.'/div';
                $output .= 'div class="right"a href="'.esc_url($get_post_link).'"span class="wpcr_title" title=""'.get_the_title().'/span/a';
                $output .= $avg_bef.'div class="desc"'.$excerpt.'/div'.$avg_aft.'/div/li';
                echo $output;
        $i++;
    }
    echo '/ul/div';

    wp_reset_query();
  } else {
    echo 'Sorry, no post found.';
  }

 echo $args['after_widget'];
}

Screenshots

Topic comment-meta comment-form comments query-posts Wordpress

Category Web

1
answered at

Your code uses this:

number_format((float)$results[$i]['avg_rate'], 2, '.', '')

And if we look at where $i is set, we see this just before the loop:

     $i=0;

and this:

        $i++;

But I don't see how this maps in any way to the current post when there's a conditional, meaning that not everything in the results array is included in the post query.

Instead, don't use $i, use the post ID, and store the post ID as the key.

Some further notes:

  • This will not scale, as the number of posts increases the query will get slower, and as the number of ratings increase, the query will get slower. 2x as many posts won't mean twice as slow, it'll get slower faster than that. It'll be fast when there's not much data, and it'll grind to a halt afterwards.
  • It could be thousands of times faster and more efficient to just recalculate a posts average rating when a new rating is added, and store the average in post meta
  • wp_reset_query has no place here. Unless you're using query_posts don't use it. Perhaps you meant to use wp_reset_postdata?
  • If you're going to write raw SQL queries, prepare your statements. Don't just put variables inside the query strings, that's dangerous! $wpdb->prepare is your friend, sanitise and make those queries safe
  • There are a lot of unnecessary variables, for example why use $output? Just echo them and save the effort of another variable, it's a waste of resources, adds overhead when reading and typing out, and it has no benefit
  • $avg_bef and $avg_aft are only assigned if that position is chosen, which will generate PHP warnings ( and in PHP 8 will generate PHP fatals ), assign them a default value beforehand of $avg_bef = ''; etc, don't just pull them out of thin air

About

Geeks Mental is a community that publishes articles and tutorials about Web, Android, Data Science, new techniques and Linux security.