Select min/max meta from query

Question

Kind of lost on what to do here. I have a meta price for each post. I'd like to get the min and max price meta values of this search/query. At the moment its returning the min max values for all posts. Probably a stupid question but how do you query the query? or another solution along these lines.

    // get and display lowest price from search query ?
     $query = "SELECT min(cast(meta_value as unsigned)) FROM wp_postmeta WHERE meta_key='_wp_price' AND meta_value IS NOT NULL AND meta_value <> 0";
$the_min = $wpdb->get_var($query);
   echo '<span class="pricerange">Price range: '.ft_currency_conv(of_get_option('ft_currency')).''.$the_min.' to</span>';

   // get and display highest price from search query how?
    $query = "SELECT max(cast(meta_value as unsigned)) FROM wp_postmeta WHERE meta_key='_wp_price'";
$the_max = $wpdb->get_var($query);
   echo '<span class="pricerange">'.ft_currency_conv(of_get_option('ft_currency')).''.$the_max.'</span>';

    // the final search query
    $querystr = "
            SELECT DISTINCT $wpdb->posts.ID, $wpdb->posts.* 
            FROM $wpdb->posts
            LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
            LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
            LEFT JOIN $wpdb->terms ON($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id) 
            LEFT JOIN $wpdb->postmeta ON($wpdb->postmeta.post_id = $wpdb->posts.ID) 
            WHERE $wpdb->posts.post_status = 'publish' 
            AND $wpdb->posts.post_type = 'showroom' 
            ORDER BY $wpdb->posts.post_title ASC
            ";

// get results from the search query
$searchresults = $wpdb->get_results($querystr, OBJECT);

Update: The posts are already sorted, I'm trying to pull the lowest and highest meta value (price) of the search results, so I can display the price range. So how do I pull a min/ max meta value out of a final search query and display it. Something along these lines.

   $query = "
    SELECT DISTINCT {$wpdb->posts}.ID, 
    {$wpdb->posts}.*,
    (SELECT min(cast(meta_value as unsigned)) 
          FROM {$wpdb->postmeta} 
          WHERE meta_key='_wp_price' 
          AND meta_value IS NOT NULL 
          AND meta_value <> 0
          AND {$wpdb->postmeta}.post_id = {$wpdb->posts}.ID
    ) as min_price,
    FROM {$wpdb->posts}
    LEFT JOIN {$wpdb->term_relationships} ON ({$wpdb->posts}.ID = {$wpdb->term_relationships}.object_id)
    LEFT JOIN {$wpdb->term_taxonomy} ON({$wpdb->term_relationships}.term_taxonomy_id = {$wpdb->term_taxonomy}.term_taxonomy_id)
    LEFT JOIN {$wpdb->terms} ON ({$wpdb->terms}.term_id = {$wpdb->term_taxonomy}.term_id) 
    LEFT JOIN {$wpdb->postmeta} ON ({$wpdb->postmeta}.post_id = {$wpdb->posts}.ID) 
    WHERE {$wpdb->posts}.post_status = 'publish' 
    AND {$wpdb->posts}.post_type = 'showroom'"; 

   $the_min = $wpdb->get_var($query); // get the min price of search
     echo '<span class="pricerange">Price range: '.ft_currency_conv(of_get_option('ft_currency')).''.$the_min.' to</span>'; // display the min price of search above post results.

Answer 1

You need subqueries. This is really a SQL question but as I know no way to do this with native WordPress functions without looping and running multiple queries here you go.

$querystr = "
        SELECT DISTINCT {$wpdb->posts}.ID, 
        {$wpdb->posts}.*,
        (SELECT min(cast(meta_value as unsigned)) 
              FROM {$wpdb->postmeta} 
              WHERE meta_key='_wp_price' 
              AND meta_value IS NOT NULL 
              AND meta_value <> 0
              AND {$wpdb->postmeta}.post_id = {$wpdb->posts}.ID
        ) as min_price,
        (SELECT max(cast(meta_value as unsigned)) 
              FROM {$wpdb->postmeta} 
              WHERE meta_key='_wp_price' 
              AND meta_value IS NOT NULL 
              AND meta_value <> 0
              AND {$wpdb->postmeta}.post_id = {$wpdb->posts}.ID
        ) as max_price,
        FROM {$wpdb->posts}
        LEFT JOIN {$wpdb->term_relationships} ON ({$wpdb->posts}.ID = {$wpdb->term_relationships}.object_id)
        LEFT JOIN {$wpdb->term_taxonomy} ON({$wpdb->term_relationships}.term_taxonomy_id = {$wpdb->term_taxonomy}.term_taxonomy_id)
        LEFT JOIN {$wpdb->terms} ON ({$wpdb->terms}.term_id = {$wpdb->term_taxonomy}.term_id) 
        LEFT JOIN {$wpdb->postmeta} ON ({$wpdb->postmeta}.post_id = {$wpdb->posts}.ID) 
        WHERE {$wpdb->posts}.post_status = 'publish' 
        AND {$wpdb->posts}.post_type = 'showroom' 
        ORDER BY {$wpdb->posts}.post_title ASC
        ";

Note: I don't have your data in my database so I can't test that but I am sure that it is at least very close to correct.