WP User Query fails when searching meta queries and search columns

admin管理员组

文章数量:1123505

I'm building an advanced User search using WP_User_Query but experiencing some unusual behaviour. I'm trying to search with a term that I know exists in the user_nicename and user_email fields but fails to return when I include meta_query in the $args.

Using the simple query below, when searching the users table data, WordPress returns everything as expected because the term exists in the user_nicename and user_email.

$args = array (
    'fields' => ['ID'],
    'count_total' => true,
    'order' => 'ASC',   
    'orderby' => 'display_name',
    'search' => '*'.esc_attr( $search_term ).'*',
);
$wp_user_query = new WP_User_Query($args);

Then however, when I introduce a meta_query into the search to check against the user meta, the query fails to return any results at all - even though it's matching data previously using the simple statement. The meta_query makes the $args look like so:

$args = array (
    'fields' => ['ID'],
    'count_total' => true,
    'order' => 'ASC',   
    'orderby' => 'display_name',
    'search' => '*'.esc_attr( $search_term ).'*',
    'meta_query' => array(
        'relation' => 'OR',
        array(
            'key'     => 'first_name',
            'value'   => $search_term,
            'compare' => 'LIKE'
        ),
        array(
            'key'     => 'last_name',
            'value'   => $search_term,
            'compare' => 'LIKE'
        ),
        array(
            'key' => 'description',
            'value' => $search_term ,
            'compare' => 'LIKE'
        )
    )
);
$wp_user_query = new WP_User_Query($args);

I'm determining which columns to search from the users table by implementing the following filter:

function inc_columns( $search_columns, $search, $this ) {
    $search_columns[] = 'user_email';
    $search_columns[] = 'user_nicename';
    $search_columns[] = 'display_name';

    return $search_columns;
}
add_filter('user_search_columns', 'inc_columns', 10, 3);

Any light that can be shed on why this is would be great.

I'm building an advanced User search using WP_User_Query but experiencing some unusual behaviour. I'm trying to search with a term that I know exists in the user_nicename and user_email fields but fails to return when I include meta_query in the $args.

Using the simple query below, when searching the users table data, WordPress returns everything as expected because the term exists in the user_nicename and user_email.

$args = array (
    'fields' => ['ID'],
    'count_total' => true,
    'order' => 'ASC',   
    'orderby' => 'display_name',
    'search' => '*'.esc_attr( $search_term ).'*',
);
$wp_user_query = new WP_User_Query($args);

Then however, when I introduce a meta_query into the search to check against the user meta, the query fails to return any results at all - even though it's matching data previously using the simple statement. The meta_query makes the $args look like so:

$args = array (
    'fields' => ['ID'],
    'count_total' => true,
    'order' => 'ASC',   
    'orderby' => 'display_name',
    'search' => '*'.esc_attr( $search_term ).'*',
    'meta_query' => array(
        'relation' => 'OR',
        array(
            'key'     => 'first_name',
            'value'   => $search_term,
            'compare' => 'LIKE'
        ),
        array(
            'key'     => 'last_name',
            'value'   => $search_term,
            'compare' => 'LIKE'
        ),
        array(
            'key' => 'description',
            'value' => $search_term ,
            'compare' => 'LIKE'
        )
    )
);
$wp_user_query = new WP_User_Query($args);

I'm determining which columns to search from the users table by implementing the following filter:

function inc_columns( $search_columns, $search, $this ) {
    $search_columns[] = 'user_email';
    $search_columns[] = 'user_nicename';
    $search_columns[] = 'display_name';

    return $search_columns;
}
add_filter('user_search_columns', 'inc_columns', 10, 3);

Any light that can be shed on why this is would be great.

• users
• meta-query
• user-meta
• wp-user-query

Share Improve this question asked Dec 8, 2016 at 11:11 estin92estin92 3366 bronze badges 1

• I should've mentioned - the search term I know doesn't appear in any of the fields referenced in the meta_query, however, I'd still expect the user to be returned because of it matching other criteria – estin92 Commented Dec 8, 2016 at 11:44

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 4

Combining a meta query with a search for a keyword will return posts which match both the search query and the result of the meta query (even if you're using relation => OR in the meta query).

This answer covers a way to achieve your expected outcome for posts, which – you need to modify the query before it fetches the result. That will need to be modified to be used with a WP User Query however. I've made an attempt at a solution that searches for the search term in the user_nicename, user_email and your meta fields - untested, so might need tweaking.

add_action( 'pre_user_query', 'user_meta_OR_search');
function user_meta_OR_search($q){
    if ($search = $q->get('_meta_or_search')){
        add_filter( 'get_meta_sql', function( $sql ) use ( $search ){
            global $wpdb;

            // Only run once:
            static $nr = 0; 
            if( 0 != $nr++ ) return $sql;

            // Modify WHERE part:
            $where = sprintf(
                " AND ( %s OR %s OR %s ) ",
                $wpdb->prepare( "{$wpdb->users}.user_nicename like '%%%s%%'", $search),
                $wpdb->prepare( "{$wpdb->users}.user_email like '%%%s%%'", $search),
                mb_substr( $sql['where'], 5, mb_strlen( $sql['where'] ) )
            );

            $sql['where'] = $where;

            return $sql;
        });
    }
}

// Then, where you do the searching:
$search_term = "test";

$args = array(
    'fields' => ['ID'],
    'count_total' => true,
    'order' => 'ASC',   
    'orderby' => 'display_name',
    '_meta_or_search' => '*'.esc_attr( $search_term ).'*',
    "meta_query" => array(
        'relation' => 'OR',
        array(
            'key'     => 'first_name',
            'value'   => $search_term,
            'compare' => 'LIKE'
        ),
        array(
            'key'     => 'last_name',
            'value'   => $search_term,
            'compare' => 'LIKE'
              ),
        array(
            'key' => 'description',
            'value' => $search_term ,
            'compare' => 'LIKE'
        )
    )
);

$the_query = new WP_User_Query($args);

Another solution is to use two queries – one which does a general search using s and one searching using meta_query:

$args1 = array (
    'fields' => ['ID'],
    'count_total' => true,
    'order' => 'ASC',   
    'orderby' => 'display_name',
    'search' => '*'.esc_attr( $search_term ).'*'
);

$wp_user_query1 = new WP_User_Query($args1);
$args2 = array (
    'fields' => ['ID'],
    'count_total' => true,
    'order' => 'ASC',   
    'orderby' => 'display_name',
    'meta_query' => array(
        'relation' => 'OR',
        array(
            'key'     => 'first_name',
            'value'   => $search_term,
            'compare' => 'LIKE'
        ),
        array(
            'key'     => 'last_name',
            'value'   => $search_term,
            'compare' => 'LIKE'
        ),
        array(
            'key' => 'description',
            'value' => $search_term ,
            'compare' => 'LIKE'
        )
    )
);
$wp_user_query2 = new WP_User_Query($args2);

$result = new WP_User_Query();
$result->results = array_unique( array_merge( $wp_user_query1->results, $wp_user_query2->results ), SORT_REGULAR );
$result->post_count = count( $result->results );

This will merge the results of the two queries into a third query containing the final search results. I haven't tested this, so it might need some tweaking!

Because Emil's answer is deprecated by now here is my update solution which uses pre_get_users filter instead of pre_users_query to attach the correct filter and also remove it again afterwards so it only runs once for this query:

add_action( 'pre_get_users', 'user_meta_OR_search');
function user_meta_OR_search($q){
    $search = $q->get("_meta_or_search");

    if (empty($search)) return $q;

    $fn = null;
    $fn = function ($sql) use ($search, &$fn) {
        global $wpdb;

        // Only run once:
        static $nr = 0;
        if( 0 != $nr++ ) return $sql;

        // Modify WHERE part:
        $where = sprintf(
            " AND ( %s OR %s OR %s ) ",
            $wpdb->prepare( "{$wpdb->users}.user_nicename like '%%%s%%'", $search),
            $wpdb->prepare( "{$wpdb->users}.user_email like '%%%s%%'", $search),
            mb_substr( $sql['where'], 5, mb_strlen( $sql['where'] ) )
        );

        $sql['where'] = $where;

        remove_filter("get_meta_sql", $fn);

        return $sql;
    };

    add_filter("get_meta_sql", $fn);
}

本文标签： WP User Query fails when searching meta queries and search columns