get minimum price variation and maximum price variation according to specific category woocommerce

4 Views

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”.

Read More

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.

Post Views: 4

Related posts

2 comments

  1. 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);

  2. 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();
?>

Comments are closed.