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

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

Madalin Ungureanu
Last Updated: 15/06/16

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.

From the blog

Related Articles

how to sell videos online

How to Sell Videos Online (5 Steps)

Author: John Hughes
Last Updated: May 30th, 2024

Videos can help you appeal to a global audience and increase engagement on your website. You might want to create and sell exclusive vlogs, education courses, or online tutorials. But, it can seem like a complicated process if you lack technical skills. With beginner-friendly platforms like WordPress and Paid Member Subscriptions, it's relatively simple to […]

Continue Reading
WordPress Email Login and Registration Setup

How to Customize WordPress Email Login Behavior (+ Registration)

Author: Colin Newcomer
Last Updated: April 14th, 2021

Searching for a WordPress email login feature so that you can customize your site's login process, such as requiring users to use their email address instead of a username? By default, WordPress lets users log in with either their username and password or their email and password via the default login page. But if you […]

Continue Reading
Roundup WordPress ecosystem May 2017

Roundup of WordPress ecosystem #4 – May 2017

Author: Patricia Borlovan
Last Updated: October 31st, 2017

There have been a lot of interesting things happening around the WordPress Community lately and we are now all curious about the WordPress 4.8 and this year's Edition of WordCamp Europe. Read about these topics and much more, on what happened around the WordPress ecosystem below. News & Updates Community News At the end of […]

Continue Reading

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

    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?

    Reply

    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!

    Reply

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.