Support » Plugins » Sorting authors by last name

  • I’m trying to figure out how to create a complete list of all users, ordered by last name. Since I have around 800 users in the DB, I’m looking for a way to JOIN the wp_users and wp_usermeta tables and hopefully, but I’m not sure how to work this with the meta key/value structure.

    Does anyone know how I could JOIN the two in a way that I can either sort the users in the SQL or in PHP withouth having to do an extra query for each user seperately?

    Thanks in advance!

Viewing 5 replies - 1 through 5 (of 5 total)
  • not sure how users get set up in the DB as there is a seperate first/last name feild by default but maybe this can help:

    This plugin could help you:

    But this will kind of get you there:

    $lastnames = $wpdb->get_col("SELECT user_id FROM $wpdb->usermeta WHERE $wpdb->usermeta.meta_key = 'last_name' ORDER BY $wpdb->usermeta.meta_value ASC");
      foreach ($lastnames as $userid) {
        $user = get_userdata($userid);
        $post_count = get_usernumposts($user->ID);
        $author_posts_url = get_author_posts_url($user->ID);
        echo '<li><a href="' . $author_posts_url . '">' . $user->user_firstname . ' ' . $user->user_lastname . '</a> (' . $post_count . ') </li>';

    Thread Starter microkid


    Thanks for your suggestions.

    The function you refer to organises the users by role but doesn’t sort them by last name.

    This is indeed a solution but I’m trying to avoid the get_userdata function because it means another database query for each individual member. This would make the page very slow. The plugin you refer to has the same problem; I implemented it and it needed over 1300 queries.

    That’s why I’m trying to bring everything together into 1 or 2 queries.

    Not sure how to get you there since multiple pieces of user information are kept in usermeta (e.g. last name, first name, and nickname) so I’m guessing you will need more than one query not matter what. I believe WordPress caches user queries.

    But here’s a JOIN you might use:

    $ud = $wpdb->get_results("SELECT * FROM $wpdb->users INNER JOIN $wpdb->usermeta ON ($wpdb->users.ID = $wpdb->usermeta.user_id) WHERE $wpdb->usermeta.meta_key = 'last_name' ORDER BY $wpdb->usermeta.meta_value ASC");
    echo "<pre>"; print_r($ud); echo "</pre>";

    Can you get authors by wp_category and sort by last_name?
    SELECT user_id FROM $wpdb->usermeta WHERE meta_key = 'wp_capabilities' AND meta_value RLIKE $this_role) ORDER BY last_name ASC
    which doesn’t work, and neither does
    SELECT user_id FROM $wpdb->usermeta WHERE meta_key = 'wp_capabilities' AND meta_value RLIKE $this_role) ORDER BY $wpdb.usermeta.last_name ASC

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Sorting authors by last name’ is closed to new replies.