WP User Query multiple AND OR query problem

by

My problem is the following I’m trying to return the following.

If 
  user_login=$name_input AND 
  referrer=25 AND 
  qualified=0
  return results 

OR if 
  passup=26 AND 
  user_login=$name_input 
  return results.

At the moment it returns the results if any of the arrays is true.
It seams that the AND is not working?

$name_search =  array(
'search_columns' => array( 'user_login' ),
'search' => '*'.esc_attr( $name_input ).'*',

'meta_query' => array(
    'relation' => 'OR',
    array(
        'relation' => 'AND',
        array(
             'key' => 'user_login',
            'value' => $name_input,
            'compare' => 'LIKE'   
        ),
        array(
            'key' => 'referrer',
            'value' => '25',
            'compare' => '='
        ),
        array(
            'key' => 'qualified',
            'value' => '0',
            'compare' => '='
        ),

    ),
    array(
        'relation' => 'AND',
        array(
            'key' => 'passup',
            'value' => '26',
            'compare' => '='
        ),
        array(
             'key' => 'user_login',
            'value' => $name_input,
            'compare' => 'LIKE'

        ),


  ),
),
 );
$user_query = new WP_User_Query( $args );

This is the SQL:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )  INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )  INNER JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id )  INNER JOIN wp_postmeta AS mt4 ON ( wp_posts.ID = mt4.post_id ) WHERE 1=1  AND wp_posts.ID NOT IN (756,1278,1369,1474,1560,1627,1679,1737) AND ( 
  ( 
    ( wp_postmeta.meta_key = 'user_login' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%bob%' ) 
    AND 
    ( mt1.meta_key = 'referrer' AND CAST(mt1.meta_value AS CHAR) = '25' ) 
    AND 
    ( mt2.meta_key = 'qualified' AND CAST(mt2.meta_value AS CHAR) = '0' )
  ) 
  OR 
  ( 
    ( mt3.meta_key = 'passup' AND CAST(mt3.meta_value AS CHAR) = '26' ) 
    AND 
    ( mt4.meta_key = 'user_login' AND CAST(mt4.meta_value AS CHAR) LIKE '%bob%' )
  )
) AND wp_posts.post_type="post" AND (wp_posts.post_status="publish" OR wp_posts.post_status="op_preview" OR wp_posts.post_author = 24 AND wp_posts.post_status="private") GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

1 Answer
1

I guess the search part is not working as you expect, because your search columns setup:

'search_columns' => 'user_login',

is within the meta query and therefore not active.

Move it out of the meta query part:

$user_query = WP_User_Query(
    [
        'search_columns' = [ 'user_login' ],   //<-- array of column names
        ... 
    ]
);

Otherwise the search columns will be user_login, user_url, user_email, user_nicename and display_name.

This is the generated SQL query for your current user query:

SELECT 
    DISTINCT 
    SQL_CALC_FOUND_ROWS wp_users.* 
    FROM wp_users 
    INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) 
    INNER JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id ) 
    INNER JOIN wp_usermeta AS mt2 ON ( wp_users.ID = mt2.user_id ) 
    INNER JOIN wp_usermeta AS mt3 ON ( wp_users.ID = mt3.user_id ) 
    INNER JOIN wp_usermeta AS mt4 ON ( wp_users.ID = mt4.user_id ) 
    WHERE 1=1 
        AND 
        ( 
            ( 
                    ( wp_usermeta.meta_key = 'user_login' 
                      AND wp_usermeta.meta_value LIKE '%test%' ) 
                AND ( mt1.meta_key = 'referrer' AND mt1.meta_value="25" ) 
                AND ( mt2.meta_key = 'qualified' AND mt2.meta_value="0" ) 
            ) 
            OR 
            ( 
                    ( mt3.meta_key = 'passup' AND mt3.meta_value="26" ) 
                AND ( mt4.meta_key = 'user_login' AND mt4.meta_value LIKE '%test%' ) 
            ) 
        ) 
        AND 
        ( 
                user_login LIKE '%test%' 
            OR user_url LIKE '%test%' 
            OR user_email LIKE '%test%' 
            OR user_nicename LIKE '%test%' 
            OR display_name LIKE '%test%'
        ) 
    ORDER BY user_login ASC

where we notice the multiple OR in the search part.

Leave a Comment