• Resolved skarck

    (@skarck)


    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?

Viewing 2 replies - 1 through 2 (of 2 total)
  • What happens when you copy/paste the sql query text into the query window of ‘mysqladmin’ on your production server ? does it give any warnings ? Any errors ?
    It is easy to have some minor difference between your machines and databases.

    Thread Starter skarck

    (@skarck)

    Thanks for pointing me in the right direction!

    I extracted the generated SQL statement by combining the $query‘s properties like

    'SELECT' . $query->query_fields . ' ' . $query->query_from . ' ' . $query->query_where . ' ' . $query->query_orderby . ' ' . $query->query_limit

    When manually requesting this in MySQL it returned the error

    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 you have to run $wpdb->query( 'SET OPTION SQL_BIG_SELECTS = 1' ); right before calling WP_User_Query.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Complex WP_User_Query call fails on production server’ is closed to new replies.