0

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; ?>

2 Answers 2

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.

2
  • I'm not ordering the authors in a for each loop; I'm sorting the cleaned array with asort. How is this different from what I am already doing?
    – Squrler
    Mar 14, 2013 at 14:31
  • 2
    $user_info = get_userdata($t->ID). it's a database query and it is executed as many time foreach loop runs. So, additional 10 database query. You wanted less database query. The info you are looking for already exist within the WP_user object returned in $blogusers. You don't need to query for it again if you follow my instruction. But you have to use two foreach loop .
    – sakibmoon
    Mar 15, 2013 at 20:16
1
$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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.