Is there way to get a list of authors by quantity of received comments?
e.g.
Author ID - 7 comments received
Author ID - 5 comments received
Author ID - 1 comment received
Arnav Joy answers:
try this
<?php
global $wpdb;
$res = $wpdb->get_results( "SELECT user_id,count(user_id) AS nb FROM $wpdb->comments group by user_id ORDER BY nb DESC" );
if(!empty($res)){
foreach ($res as $ud){
echo 'Author ID'. ' - '.$ud->user_id .' , ' .$ud->nb.' comments received'.'<br>';
}
}
?>
Igor comments:
Hi Arnav,
That works like a charm! Thank you so much! I'm really appreciated.
Igor comments:
Is there way to take into account the type of post?
Authors is a group of users who have an entries with <em>posts_type <strong>POST</strong>.</em>
Agus Setiawan answers:
hi,
you can use this plugin : http://wordpress.org/extend/plugins/top-contributors/
Igor comments:
Hi Agus,
Unfortunately, nope.
That plugin allow to display:
- Top contributors by comments
- Top author by posts
I need a top authors by comments. Just a SQL-query will be fine for me.
Reland Pigte answers:
Try this one Igor :
<?php
$query = $wpdb->get_results( "SELECT user_id, count( user_id ) AS comment_received FROM $wpdb->comments GROUP BY user_id ORDER BY comment_received DESC LIMIT 0, 10" );
if($query){
foreach ($query as $res){
echo 'Author '.$res->user_id.' - '.$res->comment_receved.' comments received'.'<br/>';
}
}
?>
I think Arnav is right..
John Cotton answers:
I'm not sure Arnav is right....I think Igor is asking for the post author who has received the most comments for his posts...
If that's what is wanted, then the SQL would be:
SELECT u.display_name, u.id, COUNT(c.comment_id) AS comment_count FROM wp_users u INNER JOIN wp_posts p ON p.post_author = u.id INNER JOIN wp_comments c ON c.comment_post_ID = p.id GROUP BY u.display_name, u.id ORDER BY comment_count DESC