别担心。我已经为你的问题找到了解决办法。您可以使用以下代码根据平均评分显示5篇排名靠前的帖子。我使用sql查询和wp\\U查询函数创建了以下函数，以获得最高平均评分的帖子。
这里是所有自定义帖子类型的更新函数。
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);
在管理发布列表仪表板中添加平均列
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;
}
}
我希望这个函数可以解决您的查询。如果此功能对您有帮助，请告诉我！
非常感谢。
