SELECT max(meta_value) FROM wp_postmeta WHERE meta_key='price'... stops working when value is over 999

Question

Overview: I am not trying to return a post. I simply want the single highest value for a particular meta_value across all posts... just the value itself.

Details: I have added a custom meta_key "price" to all my posts. The value is always an integer (no decimals or non-numeric characters). I am trying to do a query that returns the highest / largest / maximum meta_value associated with this particular meta_key.

Buggy Code

function max_meta_value(){
    global $wpdb;
    $query = "SELECT max(meta_value) FROM wp_postmeta WHERE meta_key='price'";
    $the_max = $wpdb->get_var($query);
    return $the_max;
}

Buggy Results: At first I thought the above code worked, because it does if all the meta_values are less than 999. I soon discovered that if the meta_value is greater than 999 then it is ignored. So really the code above is giving me the max(meta_value) for meta_values less than 1000.

Plea to Community: Obviously I do not know why it fails, but I have a feeling that it has something to do with how WP stores the value - perhaps its datatype related? Or maybe I should not be using $wpdb->get_var(). Any guidance will be greatly appreciated.

Answer 1

The meta_value is not of an integer type for max to return proper values. You can use mysql cast method to convert into integers as follows:

SELECT max(cast(meta_value as unsigned)) FROM wp_postmeta WHERE meta_key='price'

Answer 2

I modified the original function and KDM's solution to have a more universal function. It goes like this:

function end_meta_value( $end = "max", $meta )
{
    global $wpdb;
    $query = $wpdb->prepare( 
        "SELECT %s( cast( meta_value as UNSIGNED ) ) FROM {$wpdb->postmeta} WHERE meta_key='%s'",
        $end,
        $meta
    );
    return $wpdb->get_var( $query );
}

This way you can get both minimum and maximum values of any custom meta_value. I also changed wp_postmeta to $wpdb->postmeta to fit any prefix you use.

Note: If you want query for a digit, replace %s in the $wpdb->prepare() statement with %d.

Answer 3

I modified szajmon solution to work with wp_cache and fix the sql syntax error I get.

wpdb->prepare wraps the $end variable with quotes and that trows an error (at least in my case)

function get_min_max_meta_value( $type = 'max', $key ){

    global $wpdb;
    $cash_key = md5($key . $type);
    $results = wp_cache_get($key);

    if($results === false){

        $sql = "SELECT " . $type . "( cast( meta_value as UNSIGNED ) ) FROM {$wpdb->postmeta} WHERE meta_key='%s'";
        $query = $wpdb->prepare( $sql, $key);

        return $wpdb->get_var( $query );

    }

    return $results;
}

Answer 4

Here is a kludgy way I did it using php max array and built-in WP_Query

    function get_max_post_meta_value($category_id) {

    $args = array(  
    'post_type' => 'cpt-name',
    'post_status' => 'publish',
    'cat' => $category_id,
    'posts_per_page' => 1000, 
    );

    $max_meta_value_array = array();

    $loop = new WP_Query( $args ); 

    while ( $loop->have_posts() ) : $loop->the_post(); 

            array_push($max_meta_value_array, get_post_meta( get_the_ID(), 'post_meta_name', true)  );

    endwhile;

    wp_reset_postdata(); 

    return max($max_meta_value_array) ; 

    }