'Filter WooCommerce products with post__in and additional meta queries

I am trying to find products that are on sale (including product variations) in addition to another meta_query of price under X.

The wc_get_product_ids_on_sale() function will return the IDs of products (including variations) that are on sale. Therefore I added this to post__in.

$query_args['post__in'] = wc_get_product_ids_on_sale();

However, this will override my second part of the query which is to find products under £X

$query_args = array(
    'relation' => 'AND',
    'price' => array(
        'key' => '_price',
        'value' => 15,
        'compare' => '<',
        'type' => 'numeric'
    ),
    'stock_status' => array(
        'key' => '_stock_status',
        'value' => 'instock',
        'compare' => '=',
    ),
);

return $query_args;

$args = array(
    'post_type' => array('product', 'product_variation'),
    'post__not_in' => array( $product_id ),
    'posts_per_page' => 12,
    'return' => 'ids',
    'post__in' => wc_get_product_ids_on_sale(),
    'meta_query' => $meta_query; // this contains the price under X query
);

I believe post__in is telling WP_Query to only return those posts regardless of any other query.

Are there appropriate methods to do these queries separately and combine them in some way?

phpwordpresswoocommerceproducthook-woocommerce


Solution 1:[1]

$on_sale_product_ids = wc_get_product_ids_on_sale();

$queried_products = wc_get_products(array(
    'limit' => 1000,
    'orderby' => 'id',
    'order' => 'asc',
    'price_and_stock' => true,
));
$on_sale_products = wc_get_products(array('include' => array_values($on_sale_product_ids) , 'limit' => count($on_sale_product_ids)) );

$total_products = array_merge($on_sale_products,$queried_products );

Add below code snippet for price_and_stock combination query.

function handle_custom_query_var( $query, $query_vars ) {
    if ( ! empty( $query_vars['price_and_stock'] ) ) {
        $query['meta_query'][] =    array( 'relation' => 'AND',
    'price' => array(
        'key' => '_price',
        'value' => 9,
        'compare' => '<',
        'type' => 'numeric'
    ),
    'stock_status' => array(
        'key' => '_stock_status',
        'value' => 'instock',
        'compare' => '=',
    )
            );
    }

    return $query;
}
add_filter( 'woocommerce_product_data_store_cpt_get_products_query', 'handle_custom_query_var', 10, 2 );

More details on the custom query can be found here

== Alternate ==

Note: you cannot combine post__in and post__not_in in the same query. post__in and post__not_in are mutually exclusive.

$meta_query = array(
                'relation' => 'AND',
                'price' => array(
                    'key' => '_price',
                    'value' => 9,
                    'compare' => '<',
                    'type' => 'numeric'
                ),
                'stock_status' => array(
                    'key' => '_stock_status',
                    'value' => 'instock',
                    'compare' => '=',
                ),
            );
        
            $args = array(
                'post_type' => array('product', 'product_variation'),
                'posts_per_page' => 1000,
                'return' => 'ids',
                'post__in' => wc_get_product_ids_on_sale(),
                'meta_query' => $meta_query // this contains the price under X query
            );
            $total_products = wc_get_products($args);

Solution 2:[2]

Conflict does not happen because of the combination of meta_query and post__in, it happens because post__not_in and post__in can't work in the same query. Source: https://developer.wordpress.org/reference/classes/wp_query/#post-page-parameters

If you wish to search through products on sale together with excluding one product in the same query, you can try array_diff them together and add them to post__in only.

Example:

$args = array(
    'post_type' => array('product', 'product_variation'),
    'posts_per_page' => 12,
    'return' => 'ids',
    'post__in' => array_diff(wc_get_product_ids_on_sale(), [ $product_id ]),
    'meta_query' => $meta_query; // this contains the price under X query
);

This way your are telling your query to search through products that are on sale, excluding the one with $product_id. Just make sure your wc_ function always returns an array or else this could fail.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Marfee

Related Questions