2

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.

3

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.

  • In regards to the case insensitive bit, I don't want to actually values in the table to be case insensitive, I just want the query not to regard case. – Wold Oct 25 '14 at 1:51
  • The added filter didn't work, but I'm not sure I'm using it properly. – Wold Oct 25 '14 at 2:30
  • @Wold that's weird, I tested here locally the filter. But you need to pass the replace_and on the meta_query array. Otherwise this would be applied to all queries. – Bordoni Oct 25 '14 at 3:54
  • It did end up working, I forgot to accept. I was wondering though if you can answer my comment about the case sensitive clause and if you could possibly update your answer to also search the posts for stories by a n author searched – Wold Oct 29 '14 at 0:39
  • So it's possible to CAST Single values as Case Insensitive but as this is not something that's easily done. For that to happen we would need a filter inside of the WP_Meta_Query and WP_Query to allow it, otherwise we could mess up some complex queries by using the search and replace. You can also Alter just a single column to be case insensitive. – Bordoni Oct 29 '14 at 0:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.