How to get only simple products and product variations (variable products excluded) based on a specific category in WooCommerce?

Question

I need to retrieve all simple products and product variations that:

Have post_status equal to publish;
Simple products and product variations belong to a specific product category (in my case mobile);

But excluding the variable products.

So far I have tried this code but it doesn't work:

$arg_product_filter = array(
    'nopaging'       => true,
    'post_status'    => array( 'publish' ),
    'posts_per_page' => -1,
    'post_type'      => array( 'product', 'product_variation' ),
    'tax_query'      => array(
        'relation' => 'AND',
        array(
            'taxonomy' => 'product_type',
            'field'    => 'slug',
            'terms'    => array( 'simple', 'variation' ),
        ),
        array(
            'taxonomy' => 'product_cat',
            'field'    => 'slug',
            'terms'    => 'mobile',
        )
    ),
);
$the_query = new WP_Query( $arg_product_filter );

This query returned only simple products and if I change the relation to 'OR' it returns variable products as well. What I am doing wrong?

Vincenzo Di Gaetano · Answer 1 · 2022-01-29T17:09:39.463

You can use the following custom function to get the IDs of simple products and product variations (excluding variable products).

Also, the function only returns products with the publish status by filtering them based on the product category specified as an argument.

The function will return an array containing the product ids.

/**
 * Gets the ids of simple products and product variations with the following criteria:
 * - Only if the product is published;
 * - Excludes variable products;
 * - Products belong to a specific product category.
 * 
 * @param mixed  $term     Term to search for.
 * @param string $taxonomy Taxanomy in which to search for the term.
 * @param string $type     Type of term to search for. It can contain the following values: 'term_id', 'name' or 'slug' 
 * 
 * @return array Product IDs.
 */
function get_simple_products_and_product_variations_ids_by_cat( $term, $type = 'slug', $taxonomy = 'product_cat' ) {
    global $wpdb;
    $sql = "SELECT ID
    FROM {$wpdb->prefix}posts
    INNER JOIN {$wpdb->prefix}term_relationships as tr ON ( ID = tr.object_id OR 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 post_type IN ( 'product', 'product_variation' )
    AND post_status = 'publish'
    AND ID NOT IN (
        SELECT DISTINCT post_parent AS parent_id
        FROM {$wpdb->prefix}posts
        WHERE post_type = 'product_variation'
        AND post_status = 'publish'
    )
    AND tt.taxonomy = '$taxonomy'
    AND t.$type = '$term'";
    $results = $wpdb->get_col( $sql );

    return array_map( 'intval', $results );

}

The code has been tested and works. Add it to your active theme's functions.php.

USAGE

Filtering products based on product category id (for example 30):

get_simple_products_and_product_variations_ids_by_cat( 30, 'term_id' );

Filtering products by product category name (for example Red wines):

get_simple_products_and_product_variations_ids_by_cat( 'Red wines', 'name' );

Filtering products based on the product category slug (for example red-wines):

get_simple_products_and_product_variations_ids_by_cat( 'red-wines', 'slug' );

ANSWER

The reason you only get simple products is that there is no relationship between product category and product variation. The only relationship is with variable product (post_parent of the product variation).

You can get the products by setting the post__in parameter of the WP_Query class with the result of the custom function above, then:

$args = array(
    'nopaging'       => true,
    'posts_per_page' => -1,
    'post_type'      => array( 'product', 'product_variation' ),
    'post__in'       => get_simple_products_and_product_variations_ids_by_cat( 'fruit' ),
);
$loop = new WP_Query( $args );

if ( $loop->have_posts() ) {
    while ( $loop->have_posts() ) {
        $loop->the_post();
        global $product;

        // do stuff

    }
}

RELATED ANSWERS

Product variation WP_Query with a product category in Woocommerce

How to get only simple products and product variations (variable products excluded) based on a specific category in WooCommerce?

1 Answers1

ANSWER