How to query "variable products with sale price" in woocommerce?

Question

How to query "variable products with sale price which are in stock" in woocommerce?

i didnt get any result so far. i use Jet-Engine to create custom query but it would be enough for me if someone knows the related meta-keys to query such products

Answer 1

Querying variable products with a sale price that are in stock in WooCommerce involves some complexities because the stock status and sale price are often stored at the variation level, not at the parent product level. You'll need to make use of WordPress's WP_Query or WooCommerce's WC_Product_Query and consider the metadata for both parent products and their variations.

Here are the relevant meta keys you might consider:

• _stock_status - Meta key for stock status. 'instock', 'outofstock', or 'onbackorder'.
• _price - Regular price.
• _sale_price - Sale price.

For variable products:

• _stock_status is generally set at the variation level.
• _price in the parent product is usually a range, e.g., "10-20".
• _sale_price is set at the variation level.

Here is a code snippet for querying variable products that are on sale and in stock. This query is relatively direct but keep in mind it may not handle all edge cases and might require optimization for performance:

$args = array(
    'post_type'      => 'product',
    'posts_per_page' => -1,
    'tax_query'      => array(
        array(
            'taxonomy' => 'product_type',
            'field'    => 'slug',
            'terms'    => 'variable', // Only fetch variable products
        ),
    ),
    'meta_query'     => array(
        'relation' => 'AND',
        array(
            'key'     => '_stock_status',
            'value'   => 'instock',
            'compare' => '=',
        ),
        array(
            'key'     => '_price',
            'value'   => 0,
            'compare' => '>',
            'type'    => 'NUMERIC',
        ),
    ),
);

$products = new WP_Query($args);

if ($products->have_posts()) {
    while ($products->have_posts()) {
        $products->the_post();
        wc_get_template_part('content', 'product');
    }
    wp_reset_query();
} else {
    echo 'No products found';
}