Search filter between promo and exact price

Question

I've got lots of posts with promotional prices and the rest have an exact price. I just wanted to make sure that if promotional price exists, use the promotional price as the target data when I try to submit a value from the input range else use the real price.

So in my form, I have this slider:

<input class="price-select" name="price" value="5000" type="range" min="0" max="5000">

and my query looks like this, but it's not working.

$args = [
   'posts_per_page' => -1,
   'post_status' => 'publish',
   'meta_query' => []
];

if (isset($date)) {...}

if (isset($destination)) {...}

if (isset($price)) {
        array_push($args['meta_query'], [
            'key'     => 'promotion_price',
            'value'   => $price,
            'type'    => 'numeric',
            'compare' => '<=',
        ]);
        array_push($args['meta_query'], [
            'key'     => 'exact_price',
            'value'   => $price,
            'type'    => 'numeric',
            'compare' => '<=',
        ]);
    }

    $getPackages = new WP_Query( $args );

This one works fine but it doesn't work with what I wanted, as I can't think of any solution.

I tried using BETWEEN and IN but it's not working. I'm not sure how it works in this situation.

Adding more information:

I've got lots of search filters and the way I added them is by using array_push. I tried doing this code below but it affects the other search filter and returned empty. Tried doing the 'relation' => 'OR', it works for the rest of the search filters but the price and promotion price are not working.

$args = [
   'posts_per_page' => -1,
   'post_status' => 'publish',
   'meta_query' => [
       'relation' => 'AND'
   ]
];

Any thoughts would be appreciated! Thanks!

Array dump from $args

Without Relation on meta_query

array(6) {
  ["post_type"]=>
  string(15) "post"
  ["posts_per_page"]=>
  int(4)
  ["post_status"]=>
  string(7) "publish"
  ["tax_query"]=>
  array(0) {
  }
  ["meta_query"]=>
  array(2) {
    [0]=>
    array(4) {
      ["key"]=>
      string(34) "promotion_price"
      ["value"]=>
      string(3) "439"
      ["type"]=>
      string(7) "numeric"
      ["compare"]=>
      string(2) "<="
    }
    [1]=>
    array(4) {
      ["key"]=>
      string(24) "exact_price"
      ["value"]=>
      string(3) "439"
      ["type"]=>
      string(7) "numeric"
      ["compare"]=>
      string(2) "<="
    }
  }
  ["paged"]=>
  int(1)
}

With Relation on meta_query

array(6) {
  ["post_type"]=>
  string(15) "post"
  ["posts_per_page"]=>
  int(4)
  ["post_status"]=>
  string(7) "publish"
  ["tax_query"]=>
  array(0) {
  }
  ["meta_query"]=>
  array(3) {
    ["relation"]=>
    string(2) "OR"
    [0]=>
    array(4) {
      ["key"]=>
      string(34) "promotion_price"
      ["value"]=>
      string(3) "456"
      ["type"]=>
      string(7) "numeric"
      ["compare"]=>
      string(2) "<="
    }
    [1]=>
    array(4) {
      ["key"]=>
      string(24) "exact_price"
      ["value"]=>
      string(3) "456"
      ["type"]=>
      string(7) "numeric"
      ["compare"]=>
      string(2) "<="
    }
  }
  ["paged"]=>
  int(1)
}

All of my array arguments and search filters:

$args = [
        'post_type'  => 'post',
        'posts_per_page' => 4,
        'post_status' => 'publish',
        'tax_query' => [],
        'meta_query' => [],
        'paged' => 1
];
if (isset($dates)) {
        array_push($args['meta_query'], [
            'key'     => 'dates',
            'compare' => 'IN',
            'value'   => $dates,
        ]);
    }


    if (isset($price)) {
        array_push($args['meta_query'], [
            'key'     => 'promotion_price',
            'value'   => $price,
            'type'    => 'numeric',
            'compare' => '<=',
        ]);
        array_push($args['meta_query'], [
            'key'     => 'exact_price',
            'value'   => $price,
            'type'    => 'numeric',
            'compare' => '<=',
        ]);
    }

    if (isset($packages)) {
        foreach ($packages as $package) {
            array_push($args['tax_query'], [
                'taxonomy' => 'tax_1',
                'field'    => 'term_id',
                'terms'    => $package,
            ]);
        }
    }

    if (isset($durations)) {
        foreach ($durations as $duration) {
            array_push($args['tax_query'], [
                'taxonomy' => 'tax_2',
                'field'    => 'term_id',
                'terms'    => $duration,
            ]);
        }
    }

    if (isset($departures)) {
        foreach ($departures as $departure) {
            array_push($args['tax_query'], [
                'taxonomy' => 'tax_3',
                'field'    => 'term_id',
                'terms'    => $departure,
            ]);
        }
    }

$getPackages = new WP_Query( $args );

Answer 1

In order to use two or more different meta fields that need to have a relationship bewteen them, you have to provide a value for the relation key. This way you can establish the relationship that must be met for your meta fields.

In your specific case, you would use the relation AND to make sure that the value of $price is greater or equal than the promotion price but also (read AND) lower or equal than the exact price.

So according to your question "Search filter between promo and exact price" your meta_query array will look something like:

'meta_query' => array(
    'relation' => 'AND',
    array(
        'key'     => 'promotion_price',
        'value'   => $price,
        'compare' => '>=',
        'type'    => 'NUMERIC'
    ),
    array(
        'key'     => 'exact_price',
        'value'   => $price,
        'compare' => '<=',
        'type'    => 'NUMERIC'
    )
),

Extract from the WP Meta Query docs:

You can optionally pass the relation key and set it to either OR or AND. It defines the relation, when there is more than one meta query (whether all of the conditions should be met, or at least one of them needs to be met)

Using multiple nested meta queries

You can also nest multiple meta queries having multiple relation keys. So you can create different conditional blocks. For example, to ignore the promotion_price key if it does not exist, but use it if it exists:

'meta_query'  => array(
    'relation' => 'OR',
    array(
        'relation' => 'AND',
        array(
            'key'     => 'promotion_price',
            'compare' => 'NOT EXISTS'
        ),
        array(
            'key'     => 'exact_price',
            'value'   => $price,
            'compare' => '<=',
            'type'    => 'NUMERIC',
        ),
    ),
    array(
        'relation' => 'AND',
        array(
            'key'     => 'promotion_price',
            'value'   => $price,
            'compare' => '>=',
            'type'    => 'NUMERIC'
        ),
        array(
            'key'     => 'exact_price',
            'value'   => $price,
            'compare' => '<=',
            'type'    => 'NUMERIC'
        ),
    )
),

And if you have more conditionals, you can keep on nesting them following the same structure. At each level you will have one 'relation' => 'OR/AND' with two or more arrays.