We can get products belonging to a product category or product tag very easily. What’s much more difficult is getting single variations based on a product category or product tag, as variations… don’t have a category or tag, which belong to its “parent” variable product instead.
In this post, we will define a function that you can reuse in your custom code whenever you need to get all variation IDs based on a given product category or product tag. You can then loop through the array and return whatever info you need. Enjoy!
PHP Snippet: Retrieve List of Variations By Product Category or Tag
/**
* @snippet Get Variation IDs By Taxonomy Term
* @how-to Get CustomizeWoo.com FREE
* @author Rodolfo Melogli
* @compatible WooCommerce 7
* @donate $9 https://businessbloomer.com/bloomer-armada/
*/
function get_variations_from_term( $term, $taxonomy, $type ) {
global $wpdb;
$ids = $wpdb->get_col( "
SELECT DISTINCT p.ID
FROM {$wpdb->prefix}posts as p
INNER JOIN {$wpdb->prefix}term_relationships as tr ON ( p.ID = tr.object_id OR p.post_parent = tr.object_id )
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 tt.term_id = t.term_id
WHERE p.post_type = 'product_variation'
AND p.post_status = 'publish'
AND p.ID NOT IN (
SELECT DISTINCT post_parent
FROM {$wpdb->prefix}posts
WHERE post_type = 'product_variation'
AND post_status = 'publish'
)
AND tt.taxonomy = '$taxonomy'
AND t.$type = '$term'
" );
return $ids;
}
You can then use the following in order to get the variation IDs by category or tag:
// BY PRODUCT CATEGORY
get_variations_from_term( 'chairs', 'product_cat', 'slug' );
get_variations_from_term( 59, 'product_cat', 'term_id' );
// ETC
// BY PRODUCT TAG
get_variations_from_term( 'shiny', 'product_tag', 'slug' );
get_variations_from_term( 'Super heavy', 'product_tag', 'name' );
// ETC
