How to display the minimum price of products in a category?

Question

WooCommerce

Hello, on the screen of the category (Tile Collections) in which there are goods of different prices, is it required that these categories indicate the minimum price of these goods? How to implement it. Simply put, to look like the second screenshot

Answer 1

function wpq_get_min_price_per_product_cat( $term_id ) {

  global $wpdb;

  $sql = "
  SELECT MIN( meta_value+0 ) as minprice
  FROM {$wpdb->posts}
  INNER JOIN {$wpdb->term_relationships} ON ({$wpdb->posts}.ID = {$wpdb->term_relationships}.object_id)
  INNER JOIN {$wpdb->postmeta} ON ({$wpdb->posts}.ID = {$wpdb->postmeta}.post_id)
  WHERE
  ( {$wpdb->term_relationships}.term_taxonomy_id IN (%d) )
    AND {$wpdb->posts}.post_type = 'product'
    AND {$wpdb->posts}.post_status = 'publish'
    AND {$wpdb->postmeta}.meta_key = '_price'
  ";

  return $wpdb->get_var( $wpdb->prepare( $sql, $term_id ) );
}


function wpq_after_subcategory( $category ) {
  if( function_exists( 'wpq_get_min_price_per_product_cat' ) ) {
    printf( "%s pricing starts at %s ", $category->name, wpq_get_min_price_per_product_cat( $category->term_id ) );
  }
}
add_action( 'woocommerce_after_subcategory', 'wpq_after_subcategory' );

Answer 2

$term = get_queried_object();
global $wpdb;
$results = $wpdb->get_col("
    SELECT pm.meta_value
    FROM {$wpdb->prefix}term_relationships as tr
    INNER JOIN {$wpdb->prefix}term_taxonomy as tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
    INNER JOIN {$wpdb->prefix}terms as t ON tr.term_taxonomy_id = t.term_id
    INNER JOIN {$wpdb->prefix}postmeta as pm ON tr.object_id = pm.post_id
    WHERE tt.taxonomy LIKE 'product_cat'
    AND t.term_id = {$term->term_id}
    AND pm.meta_key = '_price'
");
sort($results, SORT_NUMERIC);
function non_zero($a){ // игнорим пустые и 0 значения
    return($a > 0); 
}
$min_cat_price = min(array_filter($results, 'non_zero'));

echo 'от ' . $min_cat_price . ' руб.';