No so long ago Tom McFarlin covered the user get_users() and WP_User_Query in couple of blog posts. Specifically he first looked at searching for users by meta data (the second looked at an object oriented approach, using the underling WP_User_Query class).

In this post – I want to build on that original post and look at how you can perform ‘advanced’ search queries, such as: search for users with email like x and name like y. Or search for users with with log-in like a OR user url like b.

That is: allowing you to search, independently, more than one column of the *_users table.

What about searching meta? I’ve omitted including meta searches as these can already be done to via the meta_query in WP_User_Query (identical in layout to WP_Query‘s meta_query. Though you could extend this approach to allow foreign tables to be included in the search.

Usage

The code below will allow you to make use of an additional parameter in get_users() and WP_User_Query: search_query. This is deliberately like tax_query and meta_query and the format is the same: a two dimensional array containing (search) queries. Unlike taxonomy/key for tax/meta queries – you specify a column. Similarly instead of setting terms/value you set a term to search for.

The familiar relation argument is also available. This takes values ‘AND’ or ‘OR’, to set whether just one or all search queries must match something.

Unlike the meta/tax queries there isn’t a ‘flat version’ – by which I mean using (the deprecated) meta_key/meta_value pair instead of meta_query – this is covered by the native search and search_columns arguments.

Examples:

// Get users with ID like '1' (this will match 14 and 61 too!) OR user_login like 'mi' (will match admin and mike)
$args  = array(
    'search_query' =>array(
        'relation'=>'OR',
        array(
            'column'=>'ID',
            'term'=>1
        ),
        array(
            'column'=>'user_login',
            'term'=>'mi',
        ),
    ),
);

// Get users
$users = get_users( $args );


// Get gmail users with name like 'mi': 
// Search users with email like '@gmail.com' AND user_login like 'mi'
$args  = array(
    'search_query' =>array(
        'relation'=>'AND',
        array(
            'column'=>'user_mail',
            'term'=> '@gmail.com'
        ),
        array(
            'column'=>'user_login',
            'term'=>'mi',
        ),
    ),
);

// Get users
$users = get_users( $args );

I’ll be honest… I can’t think of many any application for this .

Note: Best practises dictate that I should recommend prefixing the search_query attribute name with something.

The code

Hopefully the comments explain all. The idea is to use the pre_user_query hook to parse the search_query arguments and construct additional SQL queries to the WHERE part of the statement. Of course, if searching foreign tables you would also need to extend the JOIN part.

add_action('pre_user_query','sh_advanced_user_search');
function  sh_advanced_user_search( $query ){

    global $wpdb;

    //If there is no search_query, don't do anything
    if( ! $query->query_vars['search_query'] || !is_array($query->query_vars['search_query']) )
        return;

    //Collect queries
    $searches = $query->query_vars['search_query'];

    //Set relation
    if ( isset($searches['relation']) && strtoupper($searches['relation']) == 'OR' ) {
        $relation = 'OR';
    } else {
        $relation = 'AND';
    }

    //Parse search queries
    $sql_search_queries = array();
    foreach ( $searches as $key => $sq ) {

        //Check search_query is an array, with column and term set
        if ( !is_array($sq) || !isset($sq['column']) || !isset($sq['term']) )
            continue;

        //Whitelist the column
        $whitelist = array( 'ID', 'user_login', 'user_email', 'user_url', 'user_nicename' );
        if( !in_array($sq['column'], $whitelist) )
            continue;

        //Add SQL to the $sql_search_queries array
        $search = '%' . like_escape( $sq['term'] ) . '%';
        $sql_search_queries[] = $wpdb->prepare("{$sq['column']} LIKE %s",$search);
    }

    if( $sql_search_queries ){
        $query->query_where .= ' AND ('.implode( ' '.$relation.' ', $sql_search_queries).') ';
    }
}