WP_User_Query fails on search_columns AND relation

[tareq1988]

I am searching for users with this query

<?php
$args = array(
    'search' => '*' . $search_query . '*',
    'search_columns' => array(
        'user_login',
        'user_nicename',
    ),
    'meta_query' => array(
        'relation' => 'OR',
        array(
            'key'     => 'first_name',
            'value'   => $search_query,
            'compare' => 'LIKE'
        ),
        array(
            'key'     => 'last_name',
            'value'   => $search_query,
            'compare' => 'LIKE'
        ),
        array(
            'key'     => 'description',
            'value'   => $search_query,
            'compare' => 'LIKE'
        ),
    )
);

$meta_users = new WP_User_Query( $args );

Example Search: John
User on DB:

• first_name: John
• last_name: Doe
• user_login: admin
• user_nicename: admin

Generated Query:

SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.*
FROM wp_users
INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id )
WHERE 1=1
AND (
     (wp_usermeta.meta_key = 'first_name' AND CAST(wp_usermeta.meta_value AS CHAR) LIKE '%john%' ) 
  OR ( wp_usermeta.meta_key = 'last_name' AND CAST(wp_usermeta.meta_value AS CHAR) LIKE '%john%' ) 
  OR ( wp_usermeta.meta_key = 'description' AND CAST(wp_usermeta.meta_value AS CHAR) LIKE '%john%' )
)
AND (user_login LIKE '%john%' OR user_nicename LIKE '%john%')
ORDER BY user_login ASC
LIMIT 20

The query fails because user_columns are joining with AND. Having OR in query would return the correct result. But currently there is no way to define OR because its hardcoded on the get_search_sql function. We should be able to define OR/AND by ourselves and if not defined, it should fallback to AND.

[nikolov.tmw]

The problem I see here is that it will be potentially difficult to separate out the search condition from other conditions.

For example I added the include query parameter and set it to array( 2 ). This is the resulting query:

WHERE 1=1 AND ( 
  ( 
    ( 
      ( wp_usermeta.meta_key = 'first_name' AND CAST(wp_usermeta.meta_value AS CHAR) LIKE '%John%' ) 
      OR 
      ( wp_usermeta.meta_key = 'last_name' AND CAST(wp_usermeta.meta_value AS CHAR) LIKE '%John%' ) 
      OR 
      ( wp_usermeta.meta_key = 'description' AND CAST(wp_usermeta.meta_value AS CHAR) LIKE '%John%' )
    ) 
    AND 
    ( mt1.meta_key = 'wp_capabilities' AND CAST(mt1.meta_value AS CHAR) LIKE '%\"administrator\"%' )
  )
) OR (user_login LIKE '%John%' OR user_nicename LIKE '%John%') AND wp_users.ID IN (2)

This returned the user with ID 1, because of the OR clause in between the other two clauses, effectively rendering the last clause useless( AND wp_users.ID IN (2) ).

Also as you can see I added the role parameter and that added one more clause to the meta query search.

Setting 'include' => array( 1 ), 'role' => 'subscriber' also returned the user with ID 1 - which is not correct(since they are an admin).

I don't have a good idea on how to separate things out and make the search part use OR without breaking other things :(

[boonebgorges]

Thanks for the ticket.

The behavior you're describing here is not really a bug, it's just a shortcoming of the implementation. The query is "failing" because you're asking it to do something it's not designed to do.

We should be able to define OR/AND by ourselves and if not defined, it should fallback to AND.

I agree with this in a broad sense, but it's not at all clear how it'd work. Recent improvements in tax/meta/date queries - and, in particular, their support for nested query clauses - might point in the direction of a syntax ​https://make.wordpress.org/core/2014/10/20/update-on-query-improvements-in-4-1/. Doing something like this in a top-level query class like WP_User_Query would be more complicated in a number of ways. For example, some of the query args - like 'orderby' - operate only on the aggregated results, and so can't be nested. More fundamentally, the monolithic methods currently used to build SQL clauses would have to be broken up into much smaller, reusable pieces.

I'd like for this to happen one day, but it's going to pretty much require a complete rewrite of all of our query classes :) Thoughts and comments welcome, but for now I'm closing this as maybelater.