Ask your WordPress questions! Pay money and get answers fast! Comodo Trusted Site Seal
Official PayPal Seal

SQL-query for getting a top authors by qty of received comments WordPress

  • SOLVED

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

Answers (4)

2012-06-01

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>

2012-06-01

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.

2012-06-01

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..

2012-06-01

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