Getting list of top 10 users and sort on last name

Question

I would like to display a list of the top 10 authors based on the amount of posts that they have, and then sort them on last name. To my knowledge, this is not trivial since the last name of a user resides in the user meta table.

I have been succesful in writing the code to achieve what I'm looking for, please see the code below. I am however wondering if it is possible to optimize this code, so it uses less db queries. Anyone have any suggestions?

        <?php /* 1. Get list of 10 users with the most posts, sorted by post count. */ ?>
        <?php $blogusers = get_users('exclude=1,2&order=DESC&orderby=post_count&number=10'); ?>

        <?php /* 2. Set up array with author ID's and user last names from the user meta table */ ?>
        <?php foreach($blogusers as $t){
            $user_info = get_userdata($t->ID);

            // Sanitises connecting words in last names to enable proper sorting
            $lastname = $user_info->user_lastname;
            $patterns = array();
            $patterns[0] = '/van /';
            $patterns[1] = '/het /';
            $patterns[2] = '/de /';
            $patterns[3] = '/der /';
            $patterns[4] = '/\'t /';
            $replacements = array();
            $replacements[0] = '';
            $replacements[1] = '';
            $replacements[2] = '';
            $replacements[3] = '';
            $replacements[4] = '';
            $cleaned_lastname = preg_replace($patterns, $replacements, $lastname);

            // Store cleaned last name with author ID key in array
            $poep[$t->ID] = $cleaned_lastname;
        } ?>

        <?php /* 4. Sort array based on last name */ ?>
        <?php asort($poep); ?>

        <?php /* 5. Display tiles */ ?>
        <?php $count =1;?>          

        <?php foreach ( $poep as $key => $val ) : ?>
        <?php $user = get_user_meta($key); ?>

        <a href="<?php echo get_author_posts_url($key); ?>" class="tile author_tile_link author_tile<?php if ($count % 5 == 0) { echo ' last'; } ?>">
            <?php echo get_wp_user_avatar($key, 'thumbnail'); ?>
            <div class='tile_content'>
                <h2><?php echo get_the_author_meta('first_name',$key).' '.get_the_author_meta('last_name',$key); ?></h2>
                <span class="button">Zie alle artikelen</span>
            </div>
        </a>
        <?php $count++; ?>
        <?php endforeach; ?>

Answer 1

You are arranging the users within foreach block. Why don't you arrange them first and then go with the loop for displaying info. If you do that, you will need only one query.

Do this in this order-
1. Do the query with get_users. You can add an extra parameter who=author. This will force to search only the authors. I believe the response will be better. This will return an array of WP_user Object.
2. Use a foreach loop to clean lastname
3. Sort the array of object based on their lastname. You can use usort() for this
4. At last, use another foreach for displaying everything.

Answer 2

$data = $wpdb->get->results("SELECT * FROM (SELECT wp_users.ID, display_name, user_login, user_nicename, count(wp_posts.ID) AS n, substring_index(display_name,' ',-1) AS lastname  FROM wp_users JOIN wp_posts ON wp_posts.post_author = wp_users.ID GROUP BY wp_posts.post_author ORDER BY n DESC LIMIT 10 ) AS SortedByCount ORDER BY lastname");

Now you have everything in the $data array. Properly Sorted by number of posts, and then properly sorted by lastname (auto calculated)

Here is the query at a glance

SELECT * FROM (SELECT wp_users.ID, display_name, user_login, user_nicename, count(wp_posts.ID) AS n, substring_index(display_name,' ',-1) AS lastname FROM wp_users JOIN wp_posts ON wp_posts.post_author = wp_users.ID GROUP BY wp_posts.post_author ORDER BY n DESC LIMIT 10 ) AS SortedByCount ORDER BY lastname

Best