How to search all user meta from users.php in the admin

2 Views

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.

Post Views: 2

Related posts

Leave a Reply

5 comments

  1. 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);
  }
}

  2. 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.

  4. 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' ] ) . "')";
    }

  5. This is what I came up with for WordPress 4.7.1 which adds the wildcard search to all user meta data.

    
add_action( 'pre_user_query', 'ds_pre_user_search'  );
function ds_pre_user_search( $query ) {
    global $wpdb;

    if( empty($_REQUEST['s']) ){return;}
    $query->query_from .= ' LEFT JOIN '.$wpdb->usermeta.' ON '.$wpdb->usermeta.'.user_id = '.$wpdb->users.'.ID';
    $query->query_where = "WHERE 1=1 AND (user_login LIKE '%".$_REQUEST['s']."%' OR ID = '".$_REQUEST['s']."' OR meta_value LIKE '%".$_REQUEST['s']."%')";
    return $query;
}

    Basically we are just joining the users and user_meta tables on the user id and rebuilding the WHERE clause to include the search on the meta_value column.