wordpress - 根据特定类别 woocommerce 获取最小价格变化和最大价格变化

Question

我想根据类别获取最低价格和最高价格。woocommerce 查询为我提供了最低和最高产品价格范围，但我希望它基于类别。

例如:category=“音乐，服装”。

以下是最低和最高价格的查询:

     $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' )
        ) );

请建议我如何根据所选类别获得它。

Answer 1

SQL 查询:

//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:

这就是我的做法:

<?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);
?>

我使用了'order'=>'DESC'，这样它就会按从高到低的顺序排序，因此我们可以从中得到最高的值。

如果您想要Min，请将'order'=>'DESC'更改为'order'=>'ASC'并你将得到 echo "Min :"get_post_meta($loop->posts[0]->ID, '_price', true);