Reputation: 85
I want to display the lowest simple product price in the content-oroduct_cat.php
page. The following code from Fancy Squares works for showing the lowest price but I want to show Simple products only, i.e. omit grouped products.
/* SHOW LOWEST PRICE ON CATEGORY PAGE */
//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)
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));
}
I tried using :
AND {$wpdb->posts}.product_type = 'simple'
but this didn't work. How would I display only simple products?
Upvotes: 1
Views: 1347
Reputation: 10799
You query is not working because
product_type
is not stored inposts
table it is stored interm_taxonomy
table. To get the desired you have to use Sub query, which will fetch all the simple product and the main query filter it according to category.
I have modified your wpq_get_min_price_per_product_cat()
as below
function wh_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'
AND {$wpdb->posts}.ID IN (SELECT posts.ID
FROM {$wpdb->posts} AS posts
INNER JOIN {$wpdb->term_relationships} AS term_relationships ON posts.ID = term_relationships.object_id
INNER JOIN {$wpdb->term_taxonomy} AS term_taxonomy ON term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id
INNER JOIN {$wpdb->terms} AS terms ON term_taxonomy.term_id = terms.term_id
WHERE term_taxonomy.taxonomy = 'product_type'
AND terms.slug = 'simple'
AND posts.post_type = 'product')";
return $wpdb->get_var($wpdb->prepare($sql, $term_id));
}
Code goes in functions.php
file of your active child theme (or theme). Or also in any plugin php files.
USAGE
echo wh_get_min_price_per_product_cat($cat_id);
Code is tested and works.
Reference: SQL query to check product_type in WooCommerce
Hope this helps!
Upvotes: 1