For problems with creating the “right” mysql query I suggest you get phpmyadmin to run on your blogs host. Then you can test out SQL queries that just do the job.
SELECT COUNT(b.ID) AS postsperuser, a.ID as post_id, user_nicename, b.ID as user_id FROM wp_posts AS a LEFT join wp_users AS b ON a.post_author = b.ID GROUP BY b.ID;
This example SQL query will return the number of posts per author. You can add the ordering as you like. Keep in mind that this SQL is returning all posts including unpublished ones.
Thread Starter
ofyav
(@ofyav)
Thans for your help.
I replace “$authors = $wpdb->get_results” sql query with yours so results return order by postperuser.
After changes wp_list_authors in wp-includes/author-template.php;
function wp_list_authors($args = '') {
global $wpdb;
$defaults = array(
'optioncount' => false, 'exclude_admin' => true,
'show_fullname' => false, 'hide_empty' => true,
'feed' => '', 'feed_image' => '', 'echo' => true
);
$r = wp_parse_args( $args, $defaults );
extract($r, EXTR_SKIP);
$return = '';
// TODO: Move select to get_authors().
$authors = $wpdb->get_results("SELECT COUNT(b.ID) AS postsperuser, a.ID as post_id, display_name, user_nicename, b.ID as ID FROM wp_posts AS a LEFT join wp_users AS b ON a.post_author = b.ID GROUP BY b.ID ORDER BY postsperuser DESC LIMIT 10");
$author_count = array();
foreach ((array) $wpdb->get_results("SELECT DISTINCT post_author, COUNT(DISTINCT ID) AS count FROM $wpdb->posts WHERE post_type = 'post' AND " . get_private_posts_cap_sql( 'post' ) . " GROUP BY post_author ORDER BY count DESC LIMIT 10") as $row) {
$author_count[$row->post_author] = $row->count;
}
foreach ( (array) $authors as $author ) {
$author = get_userdata( $author->ID );
$posts = (isset($author_count[$author->ID])) ? $author_count[$author->ID] : 0;
$name = $author->display_name;
if ( $show_fullname && ($author->first_name != '' && $author->last_name != '') )
$name = "$author->first_name $author->last_name";
if ( !($posts == 0 && $hide_empty) )
$return .= '<li>';
if ( $posts == 0 ) {
if ( !$hide_empty )
$link = $name;
} else {
$link = '<a href="' . get_author_posts_url($author->ID, $author->user_nicename) . '" title="' . sprintf(__("Posts by %s"), attribute_escape($author->display_name)) . '">' . $name . '</a>';
if ( (! empty($feed_image)) || (! empty($feed)) ) {
$link .= ' ';
if (empty($feed_image))
$link .= '(';
$link .= '<a href="' . get_author_rss_link(0, $author->ID, $author->user_nicename) . '"';
if ( !empty($feed) ) {
$title = ' title="' . $feed . '"';
$alt = ' alt="' . $feed . '"';
$name = $feed;
$link .= $title;
}
$link .= '>';
if ( !empty($feed_image) )
$link .= "<img src=\"$feed_image\" border=\"0\"$alt$title" . ' />';
else
$link .= $name;
$link .= '</a>';
if ( empty($feed_image) )
$link .= ')';
}
if ( $optioncount )
$link .= ' ('. $posts . ')';
}
if ( !($posts == 0 && $hide_empty) )
$return .= $link . '</li>';
}
if ( !$echo )
return $return;
echo $return;
}
and when you use “wp_list_authors(‘show_nickname=1&optioncount=1&exclude_admin=0&feed=RSS’)” the result will like this;
Someone (RSS) (30)
Body (RSS) (24)
Xuser (RSS) (15)
Alex (RSS) (7)
...
If you want you may change the LIMIT 10 in queries.
I’ll use this for most poster 10 users.
i used the code on mine but there’s a user with less posts above a user with more. i don’t get it
I know this is old and marked resolved but I thought I’d put in some info for anyone else that might come after. I had the very same problem using another code. That’s because the code was counting ALL posts including scheduled posts or drafts. You may want to try the List Author’s Plus plugin.
