Product variation WP_Query with a product category in Woocommerce

Question

With Woocommerce, I am trying to make a WP_Query for product variations post type with a product category 'Apple'.

$args = array(
    'product_cat'    => 'Apple',
    'post_type'      => array('product', 'product_variation'),
    'post_status'    => 'publish',
    'key'            => '_visibility',
    'value'          => 'visible',
    'posts_per_page' => 100,
    'taxonomy'       => 'pa_size',
    'meta_value'     => '39',
    'meta_query'     => array(
        array(
            'key'         => '_stock',
            'value'       => 0,
            'compare'     => '>'
        )
    )
);

But I can't get it work in this query. If I remove 'product_cat' => 'Apple', the query works. Why?

See also for getting products without WP_Query: https://stackoverflow.com/questions/21028830/woocommerce-get-products — CubicInfinity, Apr 20 '22 at 21:00

score 11 · Accepted Answer · edited May 08 '18 at 20:20

There is many mistakes in this WP_Query regarding Woocommerce products:

For product category and product attribute you should better use normally a tax_query instead.
For Product visibility, since Woocommerce 3, it's handled by product_visibility taxonomy for 'exclude-from-search' and 'exclude-from-catalog' terms.

Important notes about Product variations:

Product categories (or product tags) are not handled by Product variations, but by the parent Variable product
Product attributes for variations are handled as post meta data with a meta_key prepended by "attribute_" and a met_value that is the term slug.
Product visibility is not handled in product variations, as they are not displayed in archives pages.
They are not displayed in archive pages (as mentioned before).

So when using a WP_Query, you can NOT query at the same time "product" post type and "product_variation" post type as they are really different.

To make your query work for "product_variation" post type, you need a little utility function that will get the parent variable product for a product category (or any custom taxonomy as Product tags…):

// Utility function to get the parent variable product IDs for a any term of a taxonomy
function get_variation_parent_ids_from_term( $term, $taxonomy, $type ){
    global $wpdb;

    return $wpdb->get_col( "
        SELECT DISTINCT p.ID
        FROM {$wpdb->prefix}posts as p
        INNER JOIN {$wpdb->prefix}posts as p2 ON p2.post_parent = p.ID
        INNER JOIN {$wpdb->prefix}term_relationships as tr ON p.ID = 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'
        AND p.post_status = 'publish'
        AND p2.post_status = 'publish'
        AND tt.taxonomy = '$taxonomy'
        AND t.$type = '$term'
    " );
}

Code goes in function.php file of your active child theme (or active theme). Tested and works. Necessary for the WP_Query below…

Here WP_Query code for Product variations (only) related to a specific product category and specific variation attribute values:

// Settings
$cat_name = 'Apple'; // Product category name
$attr_taxonomy = 'pa_size'; // Product attribute
$attribute_term_slugs = array('39'); // <== Need to be term SLUGs

$query = new WP_Query( array(
    'post_type'       => 'product_variation',
    'post_status'     => 'publish',
    'posts_per_page'  => 100,
    'post_parent__in' => get_variation_parent_ids_from_term( $cat_name, 'product_cat', 'name' ), // Variations
    'meta_query'      => array(
        'relation'    => 'AND',
        array(
            'key'     => '_stock',
            'value'   => 0,
            'compare' => '>'
        ),
        array( 
            'key'     => 'attribute_'.$attr_taxonomy, // Product variation attribute
            'value'   => $attribute_term_slugs, // Term slugs only
            'compare' => 'IN',
        ),
    ),
) );

// Display the queried products count
echo '<p>Product count: ' . $query->post_count . '<p>';

// Displaying raw output for posts
print_pr($query->posts);

Tested and works.

@Geme I hope that this explanation will be useful for everybody… — LoicTheAztec, Mar 18 '18 at 14:24
I've been looking for an answer for more than an hour, and this is really the best one. thanks — Sina Salek, Feb 14 '19 at 14:57

score 0 · Answer 2 · answered Aug 08 '21 at 16:35

There are some ways to do this, you can loop products and check for if the product is variable and if true, you can add another loop to also include the variations, but if you must do it with WP_Query for any reason, you can use posts_join filter, when you use tax_query it will add a LEFT JOIN sql statement that will compare post ID column in wp_posts table with term relationship object_id column in wp_term_relationships table, the statement looks like this:

LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)

All we need to do here is add a OR statement that will make the query look also for post parent ids and make the statement like below: (in Woocommerce product variations have a post parent which is the product itself)

LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id OR wp_posts.post_parent = wp_term_relationships.object_id)

Long story, short, how do we do it?

We will use a posts_join filter to do this, but remember that we need to remove the filter immediately after wp_query is triggered (you can also make some unique variables and only change join statement if that variable is present

function dornaweb_include_parent_categories_join($joins, $wp_query) {
    /*
     * You can make a condition to make sure this code will only run on your intended query
     * Altough removing the filter after wp_query also do it
     */
    if ($wp_query->query['my_include_parent_posts_cat_variable']) {
        global $wpdb;
        $find = "{$wpdb->prefix}posts.ID = {$wpdb->prefix}term_relationships.object_id";
        $joins = str_replace($find, $find . " OR {$wpdb->prefix}posts.post_parent = {$wpdb->prefix}term_relationships.object_id", $joins);
    }

    return $joins;
}

add_filter( 'posts_join' , "dornaweb_include_parent_categories_join", 99, 2);

$products_list = new \WP_Query([
    'post_type'         => ['product', 'product_variation'],
    'posts_per_page'    => 15,
    'paged'             => !empty($_GET['page']) ? absint($_GET['page']) : 1,
    'tax_query'         => [
        [
            'taxonomy'  => 'product_cat',
            'terms'     => [$category],
        ]
    ],
    'my_include_parent_posts_cat_variable' => true
]);

remove_filter( 'posts_join' , "dornaweb_include_parent_categories_join", 99, 2);

Product variation WP_Query with a product category in Woocommerce

2 Answers2

Long story, short, how do we do it?

Linked