Top rated posts Average rating issue

Question

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></a><span 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

enter image description here

enter image description here

0
, , , Shoaib Saleem 2 years 2020-01-08T08:40:19-05:00 0 Answers 78 views 0

Leave an answer

Browse
Browse