Reputation: 259
I want to get minimum price and maximum price based on categories. The woocommerce query which gives me minimum and maximum product price range but i want it on the bases of category.
for example: category="music,clothing".
Here is query for minimum and maximum price:
$min = floor( $wpdb->get_var(
$wpdb->prepare('
SELECT min(meta_value + 0)
FROM %1$s
LEFT JOIN %2$s ON %1$s.ID = %2$s.post_id
WHERE meta_key IN ("' . implode( '","', apply_filters( 'woocommerce_price_filter_meta_keys', array( '_price', '_min_variation_price' ) ) ) . '")
AND meta_value != ""
', $wpdb->posts, $wpdb->postmeta )
) );
$max = ceil( $wpdb->get_var(
$wpdb->prepare('
SELECT max(meta_value + 0)
FROM %1$s
LEFT JOIN %2$s ON %1$s.ID = %2$s.post_id
WHERE meta_key IN ("' . implode( '","', apply_filters( 'woocommerce_price_filter_meta_keys', array( '_price' ) ) ) . '")
', $wpdb->posts, $wpdb->postmeta, '_price' )
) );
Please suggest me how can i get it according to category selected.
Upvotes: 2
Views: 3705
Reputation: 744
Rohil_PHPBeginner's answer is correct, but you do no need to get prices for all products to find the maximum. You can just alter the "posts_per_page" to be 1:
<?php
$category = array('t-shirt');
$args = array(
'posts_per_page' => 1,
'post_type' => 'product',
'orderby' => 'meta_value_num',
'order' => 'DESC',
'tax_query' => array(
array(
'taxonomy' => 'product_cat',
'field' => 'slug',
'terms' => $category,
'operator' => 'IN'
)
),
'meta_query' => array(
array(
'key' => '_price',
)
)
);
$loop = new WP_Query($args);
echo "Max :" get_post_meta($loop->posts[0]->ID, '_price', true);
wp_reset_postdata();
?>
Upvotes: 2
Reputation: 6080
SQL Query:
//SQL Query to get max price :
SELECT max(meta_value + 0) FROM wp_posts LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE meta_key = '_price' AND (wp_term_relationships.term_taxonomy_id IN (46,47));
//SQL Query to get min price :
SELECT min(meta_value + 0) FROM wp_posts LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE (meta_key = '_price' OR meta_key='_min_variation_price') AND (wp_term_relationships.term_taxonomy_id IN (46,47));
//46, 47 is the term id of category. So you need to have id of music and clothing
WP_Query :
This is the way how I do it :
<?php
$category = array('t-shirt');
$args = array(
'posts_per_page' => -1,
'post_type' => 'product',
'orderby' => 'meta_value_num',
'order' => 'DESC',
'tax_query' => array(
array(
'taxonomy' => 'product_cat',
'field' => 'slug',
'terms' => $category,
'operator' => 'IN'
)
),
'meta_query' => array(
array(
'key' => '_price',
)
)
);
$loop = new WP_Query($args);
echo "Max :" get_post_meta($loop->posts[0]->ID, '_price', true);
?>
I used 'order'=>'DESC'
so that it will sort by Highest to Lower and hence we can get Highest from it.
If you want to have Min, change 'order'=>'DESC'
to 'order'=>'ASC'
and you will have echo "Min :" get_post_meta($loop->posts[0]->ID, '_price', true);
Upvotes: 4