how to randomly list 5 authors with at least 3 published posts

Question

Bit of a newbie here...

As the title states, I am trying to generate a list of 5 random users (taken from authors, editors and administrators) who have published at least 3 posts each. (Also, I would like to exclude the author whose user ID is 4.)

I think I have managed to do all this (hacking together various solutions gratefully taken from useful previous posts here in wordpress.stackexchange!) but I am not sure if this is the best solution or if it's coded correctly. I use this feature on many pages throughout my website and am worried it might be too db query intensive.

<?php
global $wpdb;
$min_posts = 3; 
$user_ids = $wpdb->get_col("
SELECT `post_author` 
FROM
  (SELECT `post_author`, 
    COUNT(*) AS `count` 
    FROM {$wpdb->posts}
    WHERE `post_status`='publish'  
    GROUP BY `post_author`) 
AS `stats`
WHERE `count` >= {$min_posts} AND post_author !=4;");
shuffle($user_ids);

for($someNumber = 1; $someNumber <= 5; $someNumber++) {
$user = get_userdata($user_ids[$someNumber]); ?>

      <?php echo get_avatar( $user->ID, '60' );?>
      <h7><a href="<?php echo get_author_posts_url( $user->ID ); ?>">
        <?php echo $user->display_name ;?></a></h7>
      <hr>
<?php
}
unset($user_ids);
?>

Any help truly appreciated, many thanks!

Answer 1

You could use count_many_users_posts().

$args = array(
    'exclude' => array( 4, ),
    'fields' => 'ID',
);
$users = get_users( $args );
$user_posts = count_many_users_posts( $users );
foreach( $user_posts as $user => $posts ) {
    if( $posts < 3 ) {
        unset( $user_posts[$user] );
    }
}
$user_ids = array_keys( $user_posts );
shuffle( $user_ids );
for( $i = 0; $i <=4; $i++ ) {
    $user = get_userdata( $user_ids[$i] );
    // display your user info here
}

Reference

• count_many_users_posts()
• get_users()
• array_keys()

Answer 2

Basic Query

The following query will give you the five users with the most posts in a site. Using the HAVING and COUNT will be much faster than other queries. Especially faster than built in multi purpose WP queries.

global $wpdb;

$postCountSQL = <<<SQL
    SELECT post_author, COUNT( * ) AS count
        FROM {$wpdb->posts}
    GROUP BY post_author
    HAVING COUNT( * ) > 3
    ORDER BY count DESC 
    LIMIT 0 , 5
SQL;

$postCount = $wpdb->get_results( $postCountSQL );

Random results

Now that you want to have them randomly. It's actually quite easy using the MySQL function RAND():

SELECT post_author, COUNT( * ) AS count
    FROM {$wpdb->posts}
GROUP BY post_author
HAVING COUNT( * ) > 3
ORDER BY RAND() DESC
LIMIT 0 , 5

Excluding

To exclude a specific user by ID, we can use the NOT statement:

SELECT post_author, COUNT( * ) AS count
    FROM {$wpdb->posts}
GROUP BY post_author
HAVING COUNT( * ) > 3
    AND NOT post_author = {$id}
ORDER BY RAND( ) DESC 
LIMIT 0 , 5

The Plugin

To summon that in a small mu-plugin (so it is available per default on every site):

<?php
/**
 * Plugin Name: (#115930) Random User Post Count
 * Plugin URI:  http://wordpress.stackexchange.com/questions/115930/how-to-randomly-list-5-authors-with-at-least-3-published-posts
 * Description: Lists five random users and their posts count. Can exclude users by ID.
 * Author:      Franz Josef Kaiser <wecodemore@gmail.com>
 * Author URI:  http://unserkaiser.com
 */
defined( 'ABSPATH' ) OR exit;

function wpse115930UserPostCountRand( $minCount = 3, $userIDs = array(), $limit = 5 )
{
    ! is_array( $userIDs ) AND $userIDs = array( $userIDs );

    global $wpdb;

    $sql = <<<SQL
SELECT post_author, COUNT( * ) AS count
    FROM {$wpdb->posts}
GROUP BY post_author
HAVING COUNT( * ) > %d %s
ORDER BY RAND( ) DESC
LIMIT 0 , %d
SQL;

    $exclude = "";
    ! empty( $userIDs ) AND $exclude = $wpdb->prepare(
        " AND NOT post_author IN ( %s ) ",
        join( ",", $userIDs )
    );

    return $wpdb->get_results( $wpdb->prepare( $sql, $minCount, $exclude, $limit ) );
}

You can now simply call wpse115930UserPostCountRand( 3, array( 4 ), 5 ); anywhere in your templates to get the users who have a minimum of 5 posts, don't have the ID of 4 and limit the result to 3 users. As you can see the second argument is an array, so expanding the results to exclude more users is easy. The same goes for a different amount of users or minimum posts.

Caching results

As already mentioned, you could use the Transients API. Another and maybe even better option would be to use the WP Object Cache in above plugin. In case some sort of persistent cache like OpCode or MEMcache is involved, it will cache it to disk as well.

Have fun :)

Answer 3

I use this feature on many pages throughout my website and am worried it might be too db query intensive.

On pretty much everything I do, if I don't need "real-time" data, I use transients to cache my XML, JSON and Database calls. Check out the Transient API.