Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

The search form at the top of the users listing in the Admin area (wp-admin/users.php) is limited and does not search all of the user meta fields, such as bio, instant messenger handles, etc. I've not been able to find a plugin that can add this.

Is anyone aware of a plugin or a function I could create that could expand this search for all the date in the _usermeta DB -- ideally even extra fields create by a plugin or function.

share|improve this question

4 Answers 4

up vote 17 down vote accepted

Hi @user2041:

Clearly as you know you need to modify the search that's performed which you can do by modifying the values in the instance of the WP_User_Search class used for the search (you can find the source code at /wp-admin/includes/user.php if you'd like to study it.)

The WP_User_Search Object

Here's what a print_r() of that object looks like with WordPress 3.0.3 when searching for the term "TEST" and without any other plugins that might affect it:

WP_User_Search Object
(
  [results] => 
  [search_term] => TEST
  [page] => 1
  [role] => 
  [raw_page] => 
  [users_per_page] => 50
  [first_user] => 0
  [last_user] => 
  [query_limit] =>  LIMIT 0, 50
  [query_orderby] =>  ORDER BY user_login
  [query_from] =>  FROM wp_users
  [query_where] =>  WHERE 1=1 AND (user_login LIKE '%TEST%' OR user_nicename LIKE '%TEST%' OR user_email LIKE '%TEST%' OR user_url LIKE '%TEST%' OR display_name LIKE '%TEST%')
  [total_users_for_query] => 0
  [too_many_total_users] => 
  [search_errors] => 
  [paging_text] => 
)

The pre_user_search Hook

To modify the values of the WP_User_Search object you'll use the 'pre_user_search' hook which receives the current instance of the object; I called print_r() from within that hook to get access to its values which I displayed above.

The following example which you can copy to your theme's functions.php file or you can use in a PHP file for a plugin you are writing adds the ability to search on the user's description in addition to being able to search on the other fields. The function modifies the query_from and the query_where properties of the $user_search object which you need to be comfortable with SQL to understand.

Careful Modifying SQL in Hooks

The code in the yoursite_pre_user_search() function assumes that no other plugin has modified the query_where clause prior to it; if another plugin has modified the where clause such that replacing 'WHERE 1=1 AND (' with "WHERE 1=1 AND ({$description_where} OR" no longer works then this will break too. It's much harder to write a robust addition that cannot be broken by another plugin when modifying SQL like this, but it is what it is.

Add Leading and Trailing Spaces when Inserting SQL in Hooks

Also note that when using SQL like this in WordPress it's always a good idea to include leading and trailing spaces such a with " INNER JOIN {$wpdb->usermeta} ON " otherwise your SQL query might contain the following where there is no space before "INNER", which would of course fail: " FROM wp_postsINNER JOIN {$wpdb->usermeta} ON ".

Use "{$wpdb->table_name"} instead of Hardcoding Table Names

Next be sure to always using the $wpdb properties to reference table names in case the site has changed the table prefix from 'wp_' to something else. Thus it is better to refer to "{$wpdb->users}.ID" (with double quotes, not single ones) instead of hardcoding "wp_users.ID".

Limit the Query to Only When Search Terms Exist

Lastly be to only modify the query when there is a search term which you can test by inspecting search_term property of the WP_User_Search object.

The yoursite_pre_user_search() Function for 'pre_user_search'

add_action('pre_user_search','yoursite_pre_user_search');
function yoursite_pre_user_search($user_search) {
  global $wpdb;
  if (!is_null($user_search->search_term)) {
    $user_search->query_from .= " INNER JOIN {$wpdb->usermeta} ON " . 
      "{$wpdb->users}.ID={$wpdb->usermeta}.user_id AND " .
      "{$wpdb->usermeta}.meta_key='description' ";
    $description_where = $wpdb->prepare("{$wpdb->usermeta}.meta_value LIKE '%s'",
      "%{$user_search->search_term}%");
    $user_search->query_where = str_replace('WHERE 1=1 AND (',
      "WHERE 1=1 AND ({$description_where} OR ",$user_search->query_where);    
  }
}

Searching Each Meta Key-Value Pair Requires a SQL JOIN

Of course the likely reason WordPress doesn't let you search on usermeta fields is that each one adds a SQL JOIN to the query and to a query with too many joins can be slow indeed. If you really need to search on many fields then I'd create a '_search_cache' field in usermeta that collects all the other information into one usermeta field to require only one join to search it all.

Leading Underscores in Meta Keys tell WordPress Not to Display

Note that leading underscore in '_search_cache' tells WordPress that this is an internal value and not something to ever display to the user.

Create a Search Cache with the 'profile_update' and 'user_register' Hooks

So you'll need to hook both 'profile_update' and 'user_register' that are triggered on saving a user and registering a new user, respectively. You can grab all the meta keys and their values in those hooks (but omit those with values that are serialized or URL encoded arrays) and then concatenate them to store as one long meta value using the '_search_cache' key.

Store Meta as '|' Delimited Key-Value Pairs

I decided to grab all the key names and all their values and concatenate them into one big string with colons (":") separating the keys from the values and vertical bars ("|") separating the key-value pairs like this (I've wrapped them across multiple lines so you can them without scrolled to the right):

nickname:mikeschinkel|first_name:mikeschinkel|description:This is my bio|
rich_editing:true|comment_shortcuts:false|admin_color:fresh|use_ssl:null|
wp_user_level:10|last_activity:2010-07-28 01:25:46|screen_layout_dashboard:2|
plugins_last_view:recent|screen_layout_post:2|screen_layout_page:2|
business_name:NewClarity LLC|business_description:WordPress Plugin Consulting|
phone:null|last_name:null|aim:null|yim:null|jabber:null|
people_lists_linkedin_url:null

Enables Specialized Searches on Meta Using key:value

Adding the key and values as we did allows you to do searches like "rich_editing:true" to find everyone who has rich editing, or search for "phone:null" to find those with no phone number.

But Beware of Search Artifacts

Of course using this technique creates possibly unwanted search artifacts such as search for "business" and everyone will be listed. If this a problem then you might not want to use such a elaborate cache.

The yoursite_profile_update() Function for 'profile_update' and 'user_register'

For function yoursite_profile_update(), like yoursite_pre_user_search() above can be copied to your theme's functions.php file or you can use in a PHP file for a plugin you are writing:

add_action('profile_update','yoursite_profile_update');
add_action('user_register','yoursite_profile_update');
function yoursite_profile_update($user_id) {
  $metavalues = get_user_metavalues(array($user_id));
  $skip_keys = array(
    'wp_user-settings-time',
    'nav_menu_recently_edited',
    'wp_dashboard_quick_press_last_post_id',
  );
  foreach($metavalues[$user_id] as $index => $meta) {
    if (preg_match('#^a:[0-9]+:{.*}$#ms',$meta->meta_value))
      unset($metavalues[$index]); // Remove any serialized arrays
    else if (preg_match_all('#[^=]+=[^&]\&#',"{$meta->meta_value}&",$m)>0)
      unset($metavalues[$index]); // Remove any URL encoded arrays
    else if (in_array($meta->meta_key,$skip_keys))
      unset($metavalues[$index]); // Skip and uninteresting keys
    else if (empty($meta->meta_value)) // Allow searching for empty
      $metavalues[$index] = "{$meta->meta_key }:null";
    else if ($meta->meta_key!='_search_cache') // Allow searching for everything else
      $metavalues[$index] = "{$meta->meta_key }:{$meta->meta_value}";
  }
  $search_cache = implode('|',$metavalues);
  update_user_meta($user_id,'_search_cache',$search_cache);
}

Updated yoursite_pre_user_search() Function enabling a Single SQL JOIN for Searching All Interesting Meta Values

Of course for yoursite_profile_update() to have any effect you'll need to modify yoursite_pre_user_search() to use the '_search_cache' meta key instead of the description, which we have here (with the same caveats as mentioned above):

add_action('pre_user_search','yoursite_pre_user_search');
function yoursite_pre_user_search($user_search) {
  global $wpdb;
  if (!is_null($user_search->search_term)) {
    $user_search->query_from .= " INNER JOIN {$wpdb->usermeta} ON " . 
      "{$wpdb->users}.ID={$wpdb->usermeta}.user_id AND " . 
      "{$wpdb->usermeta}.meta_key='_search_cache' ";
    $meta_where = $wpdb->prepare("{$wpdb->usermeta}.meta_value LIKE '%s'",
      "%{$user_search->search_term}%");
    $user_search->query_where = str_replace('WHERE 1=1 AND (',
      "WHERE 1=1 AND ({$meta_where} OR ",$user_search->query_where);
  }
}
share|improve this answer
    
@MikeSchinkel Great, thorough answer! This is one of the many times on this site where I wish I could give away multiple upvotes for such a well-researched answer to a non-stock question. –  MathSmath Dec 23 '10 at 7:04
    
Thanks @MathSmath - Learning that people appreciate it is what keeps me going. :) –  MikeSchinkel Dec 23 '10 at 8:36
    
Mike, Thanks for the thorough answer! I'll work this into my theme later today and see who it goes. –  John Chandler Dec 23 '10 at 17:58
    
Mike, We're close, but..! Clearly this is getting me off the right start. Using either the single function you mention first, or the two functions to utilize the profile_update is working in terms of the ability to search and get the proper results. Unfortunately, these functions are messing with the listing when I first pull up users.php (which no search term specified). It is not showing all the users. When I click on the All filter, it only shows two (out of four), one of which is me, and when I click on the Administrators filter it doesn't any users -- not even me! Any ideas? –  John Chandler Dec 23 '10 at 20:59
    
A little more info. I edited the first function to search a field called 'company' which I added via the extra user details plugin. It works when I search for a user with a company name. But, it appears that sorting by All, with no search, is only returning the two results that have data in the company field, and not returning the users that have no data in the company field. –  John Chandler Dec 23 '10 at 21:10

FYI, for people searching on this topic (how to adjust the User panel search query) and finding this excellent page, WP_User_Search has been deprecated by WP_User_Query as of 3.1: http://codex.wordpress.org/Class_Reference/WP_User_Query.

share|improve this answer

I really appreciated MikeSchinkel's approach and thorough explanation above. This was super-helpful. I couldn't get it to work for me since pre_user_search has been deprecated and doesn't actually work in 3.2. I tried just switching it out with pre_user_query but that didn't work either. The thing is, it seems that $user_search->search_term doesn't work anymore so I just used $_GET['s']. I did some hacking away and was able to get this to work in 3.2. The only thing you need to set is your array of searchable metadata.

//Searching Meta Data in Admin
add_action('pre_user_query','yoursite_pre_user_search');
function yoursite_pre_user_search($user_search) {
    global $wpdb;
    if (!isset($_GET['s'])) return;

    //Enter Your Meta Fields To Query
    $search_array = array("customer_id", "postal_code", "churchorganization_name", "first_name", "last_name");

    $user_search->query_from .= " INNER JOIN {$wpdb->usermeta} ON {$wpdb->users}.ID={$wpdb->usermeta}.user_id AND (";
    for($i=0;$i<count($search_array);$i++) {
        if ($i > 0) $user_search->query_from .= " OR ";
            $user_search->query_from .= "{$wpdb->usermeta}.meta_key='" . $search_array[$i] . "'";
        }
    $user_search->query_from .= ")";        
    $custom_where = $wpdb->prepare("{$wpdb->usermeta}.meta_value LIKE '%s'", "%" . $_GET['s'] . "%");
    $user_search->query_where = str_replace('WHERE 1=1 AND (', "WHERE 1=1 AND ({$custom_where} OR ",$user_search->query_where);    
}

Hope this helps somebody.

share|improve this answer

Here is a solution to newest version of wordpress.

add_action( 'pre_user_query', 'yoursite_pre_user_search'  );
    function yoursite_pre_user_search( $query ) {
        $query->query_where .= "YOUR QUERY '" . str_replace("*", "%", $query->query_vars[ 'search' ] ) . "')";
    }
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.