WP_User_Query Inverse (NOT) Search

It took a LOT longer than it should have to build an efficient query for WordPress users today. The query — an WP_User_Query INVERSE search.

The list I was trying to generate was based on two things:
– All users with a meta_key field ‘account_status’ containing the status ‘active’
– EXCLUDE all users with a nicename ending with “at_slp_dot_guru”

Turns out this is a LOT more difficult than it should be due to shortcomings in the WP_User_Query class. The solution that created the least amount of data queries , and thus improved performance over other “post filter” methods was to make use of the pre_user_query action hook that is part of WP_User_Query.

The Patch

The short version of the patch — use the pre_user_query to change an “INCLUDE all users with this search value” to an EXCLUDE for those same users. This is done by using the INCLUDE search filter than “flipping the logic” by replacing the field comparison in the WordPress-generated query to NOT LIKE instead of LIKE.

/**
 * Filter out SLP dot Guru accounts.
 *
 * @param WP_User_Query $wpUserQuery
 */
public function filterOutSLPDotGuru( $wpUserQuery ) {
   $new_query_where = str_replace( "(user_nicename LIKE '" , "(user_nicename NOT LIKE '" , $wpUserQuery->query_where);
   $wpUserQuery->query_where = $new_query_where;
}

The implementation

To use this modification I build my query with intentional reversal of the primary data fields (user login, user name, user nicename, etc. are all primary WP_User_Query fields). In my case, search for all users that INCLUDE the ‘at_slp_dot_guru’ nicename.

I setup the WordPress action filter for ‘pre_user_query’, which takes the full WP_User_Query as a passed-by-reference object, to flip the logic from INCLUDE to EXCLUDE as noted above.

add_action( 'pre_user_query' , array( $this , 'filterOutSLPDotGuru' ) );
$query_args = array(
   'fields' => 'ID',
   'meta_key' => 'account_status', 'meta_value' => $this->month_end ? 'active' : $this->settings[ 'filter' ],
   'search' => '*_at_slp_dot_guru',
   'search_fields' => 'user_nicename'
);
$user_query = new WP_User_Query( $query_args );
$args['include'] = $user_query->get_results();

remove_action( 'pre_user_query' , array( $this , 'filterOutSLPDotGuru' ) );

Caveat

The above code is an over-simplified version of the filter. Since WordPress builds the search query to include ALL fields, not just the one specified in ‘search_fields’ you will need to use a regular expression to replace all of the LIKE phrases with NOT LIKE and OR with AND in the search match expression.

A huge pain — in my implementation I ended up doing this as a filter:

/**
 * Filter out SLP dot Guru accounts.
 *
 * @param WP_User_Query $wpUserQuery
 */
public function filterOutSLPDotGuru( $wpUserQuery ) {
   $wpUserQuery->query_where .= " AND ( user_nicename NOT LIKE '%_at_slp_dot_guru' ) ";
}

End Result

When the get_results() query is executed I can now see “all active accounts EXCEPT those that end with _at_slp_dot_guru”.

WordPress Feedback

Some possible ways that WordPress could extend WP_User_Query to accommodate complex inverse logic without a ton of overhead —

Add a ‘search_compare’ setting that works exactly the the META field operators: ‘!=’, ‘NOT LIKE’ along with the current search option which is hard-coded to ‘LIKE’. It is odd that primary fields do not have the same power as the search engine for meta fields. Not too mention the lack of consistency in the way the two query components are defined.

Allow user queries to process both an INCLUDE list and EXCLUDE list. Currently ‘include’ takes precedence when the parameter is used along with ‘exclude’ — intead of using if/else-if use back-to-back if statements allowing both filters to be used at the same time:

if ( ! empty( $include ) ) {
   // Sanitized earlier.
   $ids = implode( ',', $include );
   $this->query_where .= " AND $wpdb->users.ID IN ($ids)";
} elseif ( ! empty( $qv['exclude'] ) ) {
   $ids = implode( ',', wp_parse_id_list( $qv['exclude'] ) );
   $this->query_where .= " AND $wpdb->users.ID NOT IN ($ids)";
}

Update to this:

if ( ! empty( $include ) ) {
   // Sanitized earlier.
   $ids = implode( ',', $include );
   $this->query_where .= " AND $wpdb->users.ID IN ($ids)";
} 
if ( ! empty( $qv['exclude'] ) ) {
   $ids = implode( ',', wp_parse_id_list( $qv['exclude'] ) );
   $this->query_where .= " AND $wpdb->users.ID NOT IN ($ids)";
}

When search_fields is specified in a search filter…
match in WP_User_Query do NOT then build an SQL where clause that adds ALL the user fields including user_login, displayname, and all the others. Only add the specified search_field to the query.