WooCommerce search products between price range using WP_Query

Question

I am currently building my own custom advanced search functionality for a WordPress website with Woocommerce. You should be able to search using filter:

• Category
• Min/Max price

My current progress is attached below. This enables you to specify category slugs in the URL parameter. Returned will be posts that matches:

/**
 * Default arguments
 * @var array
 */

    $query = array(
        'post_status' => 'publish',
        'post_type' => 'product',
        'posts_per_page' => 10,
    );

/**
 * Category search
 */

    if(isset($_GET['categories']) && $_GET['categories']) {
        /**
         * Comma seperated --- explode
         * @var [type]
         */

            $categories = explode(',', $_GET['categories']);

        /**
         * Add "or" parameter
         */

            $query['tax_query']['relation'] = 'OR';

        /**
         * Add terms
         */

            foreach($categories as $category) {
                $query['tax_query'][] = array(
                        'taxonomy' => 'product_cat',
                        'field' => 'slug',
                        'terms' => $category,
                    );
            }
    }
/**
 * Fetch
 */

    $wp_query = new WP_Query($query);

Now, while this works great when you're searching for categories it seems it gets way more complicated when you need to search through prices.

In raw SQL, something like below would work:

SELECT DISTINCT ID, post_parent, post_type FROM $wpdb->posts
INNER JOIN $wpdb->postmeta ON ID = post_id
WHERE post_type IN ( 'product', 'product_variation' ) AND post_status = 'publish' AND meta_key = '_price' AND meta_value BETWEEN 200 AND 1000

I have no idea how I could implement this using WP_Query.

Solution 1

The solution, inspired by @Niels van Renselaar, but more clean:

$query = array(
    'post_status' => 'publish',
    'post_type' => 'product',
    'posts_per_page' => 10,
    'meta_query' => array(
        array(
            'key' => '_price',
            'value' => array(50, 100),
            'compare' => 'BETWEEN',
            'type' => 'NUMERIC'
        )
    )
);

$wpquery = WP_Query($query); // return 10 products within the price range 50 - 100

Solution 2

You can combine multiple meta_queries while using WP_Query (or get_posts, which I found to be less intrusive in usage).

http://codex.wordpress.org/Class_Reference/WP_Meta_Query

http://codex.wordpress.org/Class_Reference/WP_Query

You can combine them by doing something like this

$myposts = get_posts(
    array(
        'post_type' => array('product', 'product_variation'),
        'meta_query' => array(
            array(
                'key' => '_price',
                'value' => '200',
                'compare' => '>='
            ),
            array(
                'key' => '_price',
                'value' => '2000',
                'compare' => '<='
            )
        )
    )
);

Solution 3

If you was working inside woocommerce loop when you can use WC hook

add_action( 'woocommerce_product_query', 'example_product_query_price' );

function example_product_query_price ( $q ) {
    $meta_query = $q->get( 'meta_query' );

    $meta_query[] = array(
        'key'     => '_regular_price',
        'value'   => array(
            300000 ,
            900000
        ),
        'compare' => 'BETWEEN',
        'type'=> 'NUMERIC'
    );

    $q->set( 'meta_query', $meta_query );
}