How to Query WooCommerce Variations by Parent Product’s Category

A recent Business Bloomer Club discussion raised an interesting question: Is it possible to retrieve WooCommerce product variations based on their parent product’s category?

This isn’t straightforward since WooCommerce variations don’t directly store taxonomy data, such as categories, which is only assigned to their parent products. However, with a specific approach, you can query variations by referring back to their parent product’s category.

Here’s a guide to achieving this with an SQL solution.

Solution: Using SQL to Query Variations by Parent Category

Since variations don’t store taxonomy data, you’ll need to perform a more advanced query that joins variations with their parent products and references the parent’s category.

Steps to Implement

1. Set Up the Query: You’ll need to query the database with SQL to join the WooCommerce variations with their parent products and their respective categories.
2. Use a SQL JOIN: Here’s an example query that selects product variations based on their parent category:

   SELECT p.ID AS variation_id, p.post_title AS variation_name, p2.ID AS parent_id, p2.post_title AS parent_name
   FROM wp_posts AS p
   INNER JOIN wp_postmeta AS pm ON p.ID = pm.post_id
   INNER JOIN wp_term_relationships AS tr ON tr.object_id = pm.meta_value
   INNER JOIN wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
   INNER JOIN wp_terms AS t ON t.term_id = tt.term_id
   INNER JOIN wp_posts AS p2 ON pm.meta_value = p2.ID
   WHERE p.post_type = 'product_variation'
   AND tt.taxonomy = 'product_cat'
   AND t.slug = 'your-category-slug';

Replace your-category-slug with the actual category slug you’re targeting.

3. Fetch Results: This query fetches all variations linked to products in the specified category. You may need to adapt it slightly based on your database table prefix (default is wp_).

Additional Tips

For a code-based approach using WooCommerce functions, refer to Rodolfo Melogli’s helpful post on the topic: WooCommerce: Get Variations by Product Category.

Conclusion

Querying variations by parent product category requires either an SQL join or a custom function that links variations with their parent product data. This approach can be very useful for filtering variations based on category attributes, especially when working with complex WooCommerce setups.