WooCommerce, get lowest price in in category archive (archive-product.php)


Need to show the starting price in a category of products?  This quick snippet will help get you on your way.

When you set up your WooCommerce pages you can set your default shop page.  You can choose to show an archive of products that will show the prices, or an archive or Categories you can choose from to purchase products.  The down side with choosing the categories is that it doesn’t low a starting price for the products assigned to it, so I’ll show you hot to fix that.

First things first.  In your theme create a “woocommerce” folder in your theme.  Then move over copy and paste “content-product_cat.php” from the woocommerce plugin to the folder you just created in your theme. Now you can override the default file with your own without messing with the plugin.

In your functions.php add the following snippet:

//woocommerce get lowest price in category
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) 


      ( {$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 ) );


//end woocommerece

Then in your “content-product_cat.php” that you created inside the loop you’ll want to the following to the top:

   $cat_id = $category->term_id; //used to get the category id

Then add your function you created to get the product with the lowest price for each category

echo wpq_get_min_price_per_product_cat( $cat_id );