Query wp_usermeta alongside wp_users

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.

Related Posts: