User Query help – Listing Authors

  • Resolved andy_woz

    (@andy_woz)


    12 years, 5 months ago

    I have a select menu for a search function populated by a custom query ordered by user last name that is working fine but I have a clunky exclude to stop the admin user being listed:

    <select name="author" id="select-author">
<option value="">Select Author</option>

<?php
$excluded = "1"; //exclude users by ID
$authors = $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' AND $wpdb->usermeta.user_id NOT IN ($excluded) ORDER BY $wpdb->usermeta.meta_value ASC");
							      foreach($authors as $author):
$select .='<option value="'. $author -> user_nicename .'">' . $author -> display_name . '</option>';
endforeach;
print $select;
?>

</select>

    At some point I’m sure we are going to have more users and I don’t want to be excluding them based on their ID but I can make do by limiting the users to all those with the author role. I don’t see user_role currently listed in the resulting array for $authors. What I’d like to do is to add to the query and instead of excluding user ID’s have only users with the role of author in the results. What’s the best way to do this? I’ve tried a few things but nothing works so far……

    Thanks!

Viewing 3 replies - 1 through 3 (of 3 total)
  • Moderator bcworkz

    (@bcworkz)

    12 years, 5 months ago

    I’m unsure of the proper mySQL query to do this, but as it happens WP_User_Query has a ‘who’ argument that will query for all authors. You could use this to get your authors instead of $wpdb->get_results().

    Thread Starter andy_woz

    (@andy_woz)

    12 years, 5 months ago

    Interesting, thanks bcworkz I’ll certainly give it a shot.

    I have a duplicate thread here http://wordpress.org/support/topic/custom-query-list-users-by-last-name?replies=5 I’m going to consolidate the two as one of them was an error (I received a Bad Gateway error when posting and assumed it didn’t get through…)

    If anyone has any other input can we move it over to the other thread?

    Thanks.

    A

    Thread Starter andy_woz

    (@andy_woz)

    12 years, 3 months ago

    Ok I got this down, mostly thanks to this post:
    http://wpsmith.net/2012/wp/an-introduction-to-wp_user_query-class/

    The upshot is that you can do this with WP_user_query but it won’t let you order by last name without some modifications.

    Set up action in functions.php to allow you substitute the default order by user ID with user last_name:

    add_action( 'pre_user_query', 'wps_pre_user_query' );
/*
* Modify the WP_User_Query appropriately
*
* Checks for the proper query to modify and changes the default user_login for $wpdb->usermeta.meta_value
*
* @param WP_User_Query Object $query User Query object before query is executed
*/
function wps_pre_user_query( $query ) {
global $wpdb;
if ( isset( $query->query_vars['query_id'] ) && 'wps_last_name' == $query->query_vars['query_id'] )
$query->query_orderby = str_replace( 'user_login', "$wpdb->usermeta.meta_value", $query->query_orderby );
}

    Then construct you wp_user_query object like so (outputting as a select menu labeled last_name/first_name):

    <select name="author" id="select-author">
	<option value="0">Select Author</option>
<?php
	// prepare arguments
	$args = array(
	'meta_key' => 'last_name',
	'query_id' => 'wps_last_name',
	'role' => 'author'
	);

	// Create the WP_User_Query object
	$author_query = new WP_User_Query( $args );

	$authors = $author_query->get_results(); 

	foreach($authors as $author):
	$select .='<option value="'. $author -> user_nicename .'">' . $author -> last_name . ' ' . $author -> first_name . '</option>';
	endforeach;
	print $select;

?>
	</select>

    Hope this helps someone, sometime. Let me know if it does.

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘User Query help – Listing Authors’ is closed to new replies.