How to sort users by user role with WordPress WP User Query

Recently we started rewriting the code for our UserListing module in Profile Builder Pro to use the WP_User_Query() class for all our user queries and a problem we came across was how to sort the the users by their user role.

We started looking for a solution and we realized that the user roles are actually stored in a serialized array in the ‘wp_usermeta’ table with the meta_key ‘wp_capabilities’ and that the only difference between the ‘wp_capabilities’ user meta and a regular text meta is the serialization format: a:1:{s:10:”subscriber”;b:1;} If there would be a way to remove the extra characters before “subscriber” in the mysql ORDER by statement then we would have done it!

Luckily there are two mysql functions that can help us out: SUBSTRING_INDEX(str,delim,count) and REPLACE(str,from_str,to_str)

SUBSTRING_INDEX(string,delimiter,count)
Returns the substring from string before count occurrences of the delimiter. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delimiter.

1
2
3
4
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

We use the SUBSTRING_INDEX function to get the serialization format ‘a:1:{s:10:’ from a:1:{s:10:”subscriber”;b:1;} Once we got the string we can use the REPLACE function to remove it (replace it with an empty string)

REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

1
2
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'

Lets put this all together in a WordPress WP User Query example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$args = array(			
		'orderby'  => 'meta_value',                
                'meta_key' => 'wp_capabilities'
		);
 
$wp_user_search = new WP_User_Query( $args );
 
// User Loop
if ( ! empty( $wp_user_search ->results ) ) {
	foreach ( $wp_user_search ->results as $user ) {
		echo '<p>' . $user->display_name . '</p>';
	}
} else {
	echo 'No users found.';
}
 
function wp_user_query_modifications($query) {
    $query->query_orderby = 'ORDER by REPLACE( wp_usermeta.meta_value, SUBSTRING_INDEX( wp_usermeta.meta_value, \'"\', 1 ), \'\' ) ASC'; 
}
add_filter('pre_user_query', 'wp_user_query_modifications');

We initialized a WP_User_Query with the args ‘orderby’ and ‘meta_key’ and then we use the hook ‘pre_user_query’ to modify the mysql ORDER by syntax.

Note: when a user has multiple roles associated, only the first role will be taken into account when sorting.

Subscribe to get early access

to new plugins, discounts and brief updates about what's new with Cozmoslabs!

3 thoughts on “How to sort users by user role with WordPress WP User Query

  1. Great idea, but I would highly recommend you not do it exactly the way you have published. What you are showing will have side affects and modify all user queries, even ones that want to be ordered differently or ones that are called by plugins.

    Instead define your own “orderby” value such as “${your_prefix}_user_role” and then trigger your ordering only when that value is passed in:

    $args = array(
    ‘orderby’ => “${your_prefix}_user_role”
    );

    Then using hooks to look for "${your_prefix}_user_role" and only when it has that value make your changes to the query. Make sense?

    1. Hi Mike,

      Yes you are right. In our plugin we use it in a more complex way similarly to what you have described, but for the article I wanted to give a basic example, but I realize that it might be a problem for someone with less experience so I will update the article in the near future. Thank You!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.