1

I'm having a very specific and hard to debug problem with a complex meta_query in a WP_User_Query call:

I'm using this (simplified) code to built the query:

// custom user role
$arguments = array(
    'role' => 'member'
);

// number and offset
$arguments['number'] = 10;
$arguments['offset'] = 0;

$meta = array();
$meta['relation'] = 'AND';

// filter one (array)
$meta[] = array(
   'key'       => 'field-one',
   'value'     => $values,
   'compare'   => 'IN'
);

// filter two
$meta[] = array(
    'key'       => 'field-two',
    'value'     => 0,
    'compare'   => '='
);

// filter three
$meta[] = array(
    'key'       => 'field-three',
    'value'     => 0,
    'compare'   => '='
);

// even more filter
foreach( $filter as $key => $value ) {
    $meta[] = array(
        'key'     => 'field-' . $key,
        'value'   => $value,
        'compare' => 'LIKE'
    );
}

$arguments['meta_query'] = $meta;

// orderby
$arguments['meta_key'] = 'last_name';       
$arguments['orderby']  = 'meta_value'; 

$query = new WP_User_Query(
    $arguments
);

On my localhost (MySQL version 5.6.23) it correctly returns results and the correct $query->total_users. On the production site (MySQL version 5.5.42-37.1-log) $query->get_results() is empty, but the $query->total_users shows an arbitrary number.

When I print the WP_User_Query object I can inspect the resulting SQL query, which is identical both on my localhost and on the production server:

DISTINCT SQL_CALC_FOUND_ROWS wp_users.* [query_from] => 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 ) INNER JOIN wp_usermeta AS mt5 ON ( wp_users.ID = mt5.user_id ) [query_where] => WHERE 1=1 AND ( ( ( wp_usermeta.meta_key = 'last_name' AND ( ( mt1.meta_key = 'field-one' AND CAST(mt1.meta_value AS CHAR) IN ('professional','member','junior') ) AND ( mt2.meta_key = 'field-two' AND CAST(mt2.meta_value AS CHAR) = '0' ) AND ( mt3.meta_key = 'field-three' AND CAST(mt3.meta_value AS CHAR) = '0' ) AND ( mt4.meta_key = 'field-four' AND CAST(mt4.meta_value AS CHAR) LIKE '%my-value%' ) ) ) AND ( mt5.meta_key = 'wp_capabilities' AND CAST(mt5.meta_value AS CHAR) LIKE '%\"member\"%' ) ) ) [query_orderby] => ORDER BY wp_usermeta.meta_value ASC [query_limit] => LIMIT 20 )

Any idea what could be wrong with this?

  • Try printing/var_dumping $query->request and then copy/pasting that into your production mysql client interface and see what happens... – bonger Jul 31 '15 at 17:15
  • 1
    WP_User_Query does not have an request property but you can get the SQL statement by combining the clauses like 'SELECT' . $query->query_fields . ' ' . $query->query_from . ' ' . $query->query_where . ' ' . $query->query_orderby . ' ' . $query->query_limit . – hm711 Aug 2 '15 at 11:23
2

When executing the generated SQL statement in MySQL it gave me

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

To fix that I had to call $wpdb->query( 'SET OPTION SQL_BIG_SELECTS = 1' ); right before calling WP_User_Query.

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.