Query Users by login, meta & role

by

I’m using pre_user_query to limit the results shown on the users.php page.

I am limiting it by role & also by a meta field, so my query from like this:

$query->query_from = "
     FROM wp_users 
     INNER JOIN wp_usermeta ON (wp_users.ID = wp_usermeta.user_id) 
     INNER JOIN wp_usermeta AS mt1 ON (wp_users.ID = mt1.user_id) ";

And my where looks like this:

$query->query_where = " WHERE 1=1 
AND ( (wp_usermeta.meta_key = 'user-country' 
           AND CAST(wp_usermeta.meta_value AS CHAR) IN ($countries_list))
AND (mt1.meta_key = 'wp_capabilities' AND CAST(mt1.meta_value AS CHAR) 
    LIKE '%\"$role\"%') ) ";

This is working fine, however I would like to be able to search by login or email too. I’ve tried amending my where query to:

$query->query_where = "WHERE 1=1 
AND (user_login LIKE 'tom' OR user_email LIKE 'tom') 
AND ( (wp_usermeta.meta_key = 'user-country' 
     AND CAST(wp_usermeta.meta_value AS CHAR) IN ($countries_list))
AND (mt1.meta_key = 'wp_capabilities' 
     AND CAST(mt1.meta_value AS CHAR) LIKE '%\"$role\"%') ) ";

However that isn’t working. I imagine this is something to do with the fact that the wp_users table doesn’t have a prefix, but I’m not 100% sure.

EDIT: Just to clarify, there is no errors with the code, I’m just not getting the correct results back.

1 Answer
1

It’s not entirely your fault

The class responsible for generating the list of users does not honor the pre_user_query filter. This means that any custom column-sorting or filtering that relies on a modified (in pre_user_query) WP_User_Query object will not work. You can see what I mean in the patch below.

Admin-user-list-table ignores the filter

@@ -100,6 +100,7 @@ class WP_Users_List_Table extends WP_List_Table {

        // Query the user IDs for this page
        $wp_user_search = new WP_User_Query( $args );
+       $wp_user_search = apply_filters( 'pre_user_query', $wp_user_search );

        $this->items = $wp_user_search->get_results();

I think this is a bug or at least an oversight, but I can’t be sure what the authors’ intentions were. Perhaps it’s worth mentioning to to the WP people on github (I didn’t open a ticket about this).

WP_User_Query seems immutable

Additionally, the WP_User_Query->set() and WP_User_Query->prepare_query() methods do not do what I expect of them. This may or may not be relevant to your case. If you face any trouble (i.e ignored settings), just create a new object in the filter and return that.

function pre_user_query( $wp_user_query ) {
    if ( isset( $wp_user_query->query_vars['orderby'] ) and 
            $wp_user_query->query_vars['orderby']  === 'custom-column-slug') {

        $order = strtoupper( $_GET['order'] );
        if ( !in_array( $order , array('ASC', 'DESC') ))
            $order="DESC";

        $wp_user_query = new WP_User_Query( array( 
            'fields' =>  'all_with_meta',
            'orderby' =>  'meta_value',
            'order' => $order ,
            'meta_query' => array(
                0 => array(
                    'key' => 'custom-column-slug',
                    'compare' => 'NOT EXISTS',
                ),
                'relation' => 'OR',
                1 => array(
                    'key' => 'custom-column-slug',
                    'compare' => 'IN',
                    'value' => ['zumba', 'swing', 'jazz'],
                ),
            ),
        ));
    }
    return $wp_user_query;
}

Leave a Comment