Query wp_usermeta alongside wp_users

Question

I want to use the WordPress query functions and no external plugins for the task. Here is what I have so far:

add_filter('user_search_columns', 'user_search_columns_bd' , 10, 3);

function user_search_columns_bd($search_columns, $search, $this){
    if(!in_array('display_name', $search_columns)){
        $search_columns[] = 'display_name';
    }
    return $search_columns;
}

$args = array(
'search'         => $s,
'search_columns' => array( 'user_login', 'user_email'),
'meta_query' => array(
    'relation' => 'OR',
    0 => array(
        'key'     => 'first_name',
        'value'   => $s,
        'compare' => 'LIKE'
    ),
    1 => array(
        'key'     => 'last_name',
        'value'   => $s,
        'compare' => 'LIKE'
    )
)
);

$user_query = new WP_User_Query( $args );

This search works properly when you enter the full display_name, full user_login, or the full user_email.

I want to be able to return results if the search query contains a first or last name from the usermeta table, and I want to make all of the queries non-case sensitive (which was another issue I was having).

I'm not sure I am using meta_query properly as an argument, but I tried to follow the example on the WordPress Codex entry for WP_User_Query().

Thanks for any help or guidance.

Answer 1

This is not a simple answer, so first you will need to include one more filter to allow you to grab both the make the meta_query interact with the main query via an OR instead of a AND, check my sample of code:

add_filter('user_search_columns', 'q166419_user_search_columns' , 10, 3);

function q166419_user_search_columns($search_columns, $search, $this){
    if(!in_array('display_name', $search_columns)){
        $search_columns[] = 'display_name';
    }
    return $search_columns;
}

// Here is where the magic happens
add_filter( 'get_meta_sql', 'q166419_user_meta_filter', 10, 6 );

function q166419_user_meta_filter( $sql, $queries, $type, $primary_table, $primary_id_column, $context ){
    // If it's not user forget it!
    if ( $type !== 'user' ){
        return $sql;
    }

    // Only if our variable is true then we will do the change
    if ( ! isset( $context->query_vars['meta_query']['replace_and'] ) || $context->query_vars['meta_query']['replace_and'] !== true ){
        return $sql;
    }

    $sql['where'] = preg_replace('/AND/', 'OR', $sql['where'], 1);
    return $sql;
}

$args = array(
    'search' => $s,
    'search_columns' => array( 'user_login', 'user_email'),
    'meta_query' => array(
        'relation' => 'OR',
        'replace_and' => true, // Flag for the dark magic
        array(
            'key' => 'first_name',
            'value' => $s,
            'compare' => 'LIKE'
        ),
        array(
            'key' => 'last_name',
            'value' => $s,
            'compare' => 'LIKE'
        )
    )
);

Bonus: To resolve your Case Sensitive search you will need to apply this to you SQL tables;

ALTER TABLE wp_usermeta CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE wp_users CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

The ci at the end of the Collate stands for Case Insensitve.