1

I'm trying to compare user meta values to find highest and lowest values. I have a user meta value set up called "hourly_rate". I want to get the hourly rate for all users and then run PHP's min() and max() on these values to find the lowest and highest hourly rates across all of my users.

Currently, I'm doing this by getting all users from the database and iterating through a foreach loop, grabbing the specific meta key for each user and stashing it in an array. Then I run min and max on that array.

As the site scales in users, this seems like it could become quite cumbersome just to find a min and max value. Is there a way I can do something like get_user_meta() by key instead of ID?

For example, I currently do this:

$users = new WP_User_Query(array(
    'order' => 'DESC',
    'fields' => 'all_with_meta'
));

$hourly_rates_array = array();

foreach($users->results as $user){
    $hourly_rate = get_user_meta($user->ID, 'hourly_rate', true);
    array_push($hourly_rates_array, $hourly_rate);
}

//Find the necessary limits for each user

$lowest_hourly_rate = min($hourly_rates_array);
$highest_hourly_rate = max($hourly_rates_array);

Is there a way I could do something like this:

$hourly_rates_array = get_user_meta('ALL', 'hourly_rate', true);

$lowest_hourly_rate = min($hourly_rates_array);
$highest_hourly_rate = max($hourly_rates_array);

Which would access the wp_usermeta table directly instead of bouncing through wp_users and then wp_usermeta? This would drastically cut my DB queries and be much better for performance, but I can't seem to figure out a way to get user_meta by key instead of by user ID.

2

Two options. The first will keep you away from custom SQL, and should be a lot more efficient than what you currently have.

It runs two queries, one to get the user ID with the highest hourly rate, and then again to get the lowest. You need one more query to update the user meta cache & then you're home free:

$query = new WP_User_Query;
$users = array(
    'min_user' => 'ASC',
    'max_user' => 'DESC',
);

foreach ( $users as $var => $order ) {
    $query->query(
        array(
            'count_total' => false, // Save SQL_CALC_FOUND_ROWS, don't need it
            'meta_key' => 'hourly_rate',
            'orderby' => 'meta_value',
            'fields' => 'ID', // Don't waste memory
            'number' => 1, // Just need the first user ID of the result
            'order' => $order,
        )
    );

    if ( $data = $query->get_results() )
        $$var = $data[0]; // User ID
    else
        $$var = 0;
}

update_meta_cache( 'users', array( $min_user, $max_user ) );

echo get_post_meta( $max_user, 'hourly_rate', true ); // Highest hourly rate
echo get_post_meta( $min_user, 'hourly_rate', true ); // Lowest hourly rate

The second is one custom SQL query:

$rates = $wpdb->get_row( "SELECT MAX( CAST( meta_value AS UNSIGNED ) ) AS max_rate, MIN( CAST( meta_value AS UNSIGNED ) ) AS min_rate FROM $wpdb->usermeta WHERE meta_key = 'hourly_rate'" ); 

echo $rates->max_rate;
echo $rates->min_rate;

Doesn't get much leaner than that!

  • Bravo! This is exactly what I wanted. Thanks a bunch! – Thought Space Designs Feb 20 '15 at 17:50
  • Just tried the second SQL query you presented, and it seems to be returning incorrect values. It's setting max rate to 50 where it's actually 100. The query looks good to me but I'm not sure if maybe it's missing something. – Thought Space Designs Feb 20 '15 at 18:33
  • Probably need CAST, see update. – TheDeadMedic Feb 20 '15 at 18:49

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.