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}commentsment_post_ID), GROUP_CONCAT({$wpdb->prefix}commentsment_ID separator ', ') comment_ids FROM {$wpdb->prefix}comments JOIN {$wpdb->prefix}commentmeta ON {$wpdb->prefix}commentmetament_id = {$wpdb->prefix}commentsment_ID
WHERE {$wpdb->prefix}commentsment_approved = 1 GROUP BY {$wpdb->prefix}commentsment_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
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}commentsment_post_ID), GROUP_CONCAT({$wpdb->prefix}commentsment_ID separator ', ') comment_ids FROM {$wpdb->prefix}comments JOIN {$wpdb->prefix}commentmeta ON {$wpdb->prefix}commentmetament_id = {$wpdb->prefix}commentsment_ID
WHERE {$wpdb->prefix}commentsment_approved = 1 GROUP BY {$wpdb->prefix}commentsment_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
Share Improve this question asked Jan 8, 2020 at 12:22 Shoaib SaleemShoaib Saleem 1138 bronze badges1 Answer
Reset to default 0Your 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_queryhas no place here. Unless you're usingquery_postsdon't use it. Perhaps you meant to usewp_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->prepareis 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_befand$avg_aftare 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
发布者：admin，转转请注明出处：http://www.yc00.com/questions/1744850214a4597080.html
评论列表（0条）