it's possible make a WP_Query with math operations?

Question

have this situation. i'm building a WordPress site to sell some products. The thing is that the price of the products is stored as meta value, but the product price can be in difference currency. Now the problem is that I have to make a price filter for all product no matter in what currency is the price, the filter currency is configured in the background, and the the product that didn't have it's price in that currency have to be filter by the currency exchange tax, but i really don´t know how to build a meta_query were i can filter by currency equivalent price.

For example, I have this 2 products:

Product 1 -price:10 -currency: usd

Product 2 - price: 1000 - currency: xxx

the filter is set to use usd and find price between 0 and 10. So for this time the search will return product 1, but the currency conversion from xxx to usd is 500 to 1, the product 2 equivalent price is 2 usd the it have to be included on the search result.

until now this is the part of my code to handle the price filter:

if (
    isset($_REQUEST['max_price']) && $_REQUEST['max_price'] &&
    isset($_REQUEST['min_price']) && ($_REQUEST['min_price'] || $_REQUEST['min_price'] == 0)
) {
    $main_currency = cs_get_option('main_currency');
    $ex_currency = array_diff(['xxx', 'usd'], [$main_currency])[1];

    $args['meta_query'] [] =
        array(
            'relation' => 'AND',
            array(
                'key' => 'price_meta_key',
                'value' => array($_REQUEST['min_price'], $_REQUEST['max_price']),
                'type' => 'numeric',
                'compare' => 'BETWEEN',
            ),
            array(
                'key' => 'product_currency',
                'value' => $main_currency,
                'compare' => '='
            )
        );
}

but this only filter the product with the $main_currency currency.

Answer 1

No, not without using filters to insert raw SQL, you can do comparisons, such as greater than, smaller than, but not ranges, but even then this will be an incredibly slow query, doing it is a bad idea:

• post meta tables are optimised for finding meta when it already knows the post ID
• taxonomy tables were built for situations where you're searching for posts that have data that's already known. This is because searching post meta for posts is excruciatingly slow
• Adding a math operation prevents the database from using Indexes, forcing a table scan where it has to test each and every row to build a temporary table that the final query can be ran on

You're running head first into a tradeoff between speed and accuracy unawares, and have chosen perfect accuracy, not realising the sacrifice of speed can be extreme, and grows ever larger as the amount of post meta increases

Instead, use a taxonomy and auto-assign terms for common price ranges, and use those to filter by. That way you can still store the exact price in post meta, but you've got a much faster way to search for things by price. Additionally, the UI becomes simpler to implement