Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.
share|improve this question
    
a question. why are you doing sql query instead of WP_Query? –  Rahil Wazir Nov 20 '13 at 15:45
add comment

1 Answer

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.

share|improve this answer
    
Thanks for the reply. Actually the post query results are already sorted. What I'm trying to achieve is to display the min and max meta price of those results not the actual posts. –  steen Apr 11 '13 at 1:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.