Find the maximum price for a product category in Woocommerce / Wordpress.
This SQL query looks in a single category for the maximum price of published products. Remove AND p.post_status = "publish" to include draft products as well. Don't forget to Group By another mutual field, e.g. "product_cat".
Use IN ('category','category2','category3') instead of = "the-category-you-want" to search in multiple categories.
SELECT MAX(cast(pm.meta_value as DECIMAL) ) FROM wp_postmeta AS pm
JOIN wp_posts AS p ON pm.post_id = p.ID
JOIN wp_term_relationships AS tr ON p.ID = tr.object_id
JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms AS t on tt.term_id = t.term_id
WHERE ( pm.meta_key = "_price" OR pm.meta_key = "_max_variation_price" )
AND ( p.post_type = "product" OR p.post_type = "product_variation" )
AND p.post_status = "publish"
AND tt.taxonomy = "product_cat"
AND t.slug = "the-category-you-want"
GROUP BY p.post_status
This code has not considered the use of grouped products. This comes from a case where only single and variable products were being used.
This SQL query looks in a single category for the maximum price of published products. Remove AND p.post_status = "publish" to include draft products as well. Don't forget to Group By another mutual field, e.g. "product_cat".
Use IN ('category','category2','category3') instead of = "the-category-you-want" to search in multiple categories.
SELECT MAX(cast(pm.meta_value as DECIMAL) ) FROM wp_postmeta AS pm
JOIN wp_posts AS p ON pm.post_id = p.ID
JOIN wp_term_relationships AS tr ON p.ID = tr.object_id
JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms AS t on tt.term_id = t.term_id
WHERE ( pm.meta_key = "_price" OR pm.meta_key = "_max_variation_price" )
AND ( p.post_type = "product" OR p.post_type = "product_variation" )
AND p.post_status = "publish"
AND tt.taxonomy = "product_cat"
AND t.slug = "the-category-you-want"
GROUP BY p.post_status
This code has not considered the use of grouped products. This comes from a case where only single and variable products were being used.
