2

I have set a user metadata named achievements which is an array containing various numeric values. Using WP_User_Query, I want to order the list by a specific key within this array named points.

The following:

$args = array(
    'order' => 'DESC',
    'orderby' => 'meta_value_num',
    'meta_key' => 'achievements',
    'number' => $no,
    'offset' => $offset
);

...works if achievements would be a numeric value. How do I alter the $args array to target the key points within the metadata key achievements.

Running a print_r() of achievements for a user returns:

Array ( [x1] => 74 [x2] => 383 [x3] => 457 [x4] => 81 [points] => 301 [x5] => 382 [x6] => 12 )
2

You could save the values with the separate user meta keys:

x1, ..., x6, points

instead of the array:

achievements

to simplify your WP_User_Query() usage and not having to deal with serialized arrays as user meta values.

But I guess you've already considered this setup and wanted to avoid it ;-)

A compromise would be to move only the points out of the achievements array into a separate meta key.

Update:

When the number of users is relatively small, we can use the following (untested) PHP sorting:

$users2points = array();

$args = array(
    'meta_key' => 'achievements',
    'number'   => $no,
    'offset'   => $offset
);

$users = get_users( $args );

foreach( (array) $users as $user )
{
    $meta = get_user_meta( 'achievements', $user->ID );
    if( isset( $meta['points'] )
        $users2points[$user->ID] = $meta['points'];
}

asort( $users2points, SORT_NUMERIC );

print_r( $users2points );

Update 2:

Here's another more adventures PHP sorting that will work directly on the $users array of WP_User objects:

$args = array(
    'meta_key' => 'achievements',
    'number'   => $no,
    'offset'   => $offset
);

$users = get_users( $args );

foreach( (array) $users as $user )
{
    $meta = get_user_meta( 'achievements', $user->ID );

    // Here we assume the points values are greater or equal to 0:
    $user->points = ( isset( $meta['points'] ) ?  $meta['points'] : 0;
}

/*
usort( $users, function( $a, $b ) {
    return gmp_cmp( $a->points, $b->points );
});
*/

// The gmp_cmp() might suffer from a memory leak,
// according to some PHP bug reports,
// so let's use this instead:    

usort( $users, function( $a, $b ) {
    if( $a->points === $b->points )
        return 0;
    else
        return ( $a->points > $b->points ) ? 1 : -1;
});


print_r( $users );
  • Yes, I have considered to move 'points' out of 'achievements', but instead of adding another user metadata only for this query, I am hoping to avoid it. Is there any way to deal with this serialized array in my query? – Christine Cooper Sep 26 '14 at 16:25
  • I think you will have to use some SQL magic or move the ordering to PHP. – birgire Sep 26 '14 at 16:29
  • Thus I am hoping for a magician. =D – Christine Cooper Sep 26 '14 at 16:35
  • 1
    I updated the answer with some PHP sorting, but no magic there I'm afraid ;-) – birgire Sep 26 '14 at 17:26
  • Thank you very much for the updated examples. I do have one important question though. Is it worth while to go this route or should I simply just separate the 'points' into its own user metadata? Would that save on performance? The reason why I didn't want to do this in the first place is because I thought it would be unnecessary to add another metadata just for one query when the value exists in an array. What's your professional input? – Christine Cooper Sep 27 '14 at 12:49
1

A way to do it using SQL is to use SUBSTR and LOCATE to pick out the serialized value:

function wpse162668_pre_user_query( $query ) {
    if ( ( $orderby = $query->get( 'orderby' ) ) != 'achievements_points' ) {
        return $query;
    }
    global $wpdb;

    $order = $query->get( 'order' );
    $points_str = 's:6:"points";i:';
    $query->query_fields .= $wpdb->prepare(
        ', SUBSTR(wp_usermeta.meta_value, LOCATE(%s, wp_usermeta.meta_value) + %d)+0 AS ' . $orderby,
        $points_str, strlen( $points_str )
    );
    $query->query_orderby = 'ORDER BY ' . $orderby . ' ' . $order . ', user_login ASC';

    return $query;
}

and then change the $args orderby to 'orderby' => 'achievements_points' and wrap the get_users with the filter:

add_filter( 'pre_user_query', 'wpse162668_pre_user_query', 10, 2 );
$users = get_users( $args );
remove_filter( 'pre_user_query', 'wpse162668_pre_user_query', 10 );

The cast to integer (+0) works cos MySQL just ignores trailing non-numeric stuff. I use this method quite a lot (on postmeta, not usermeta) in varying combinations, due to using the WPAlchemy_MetaBox class with repeat groups, though it can get very messy.

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.