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.