Is it possible to apply filter to meta key value when querying posts?

Question

I am querying my posts and I am applying pricing orderby sorting filters below.

But my product_price_from meta field contains a number which is inputted as various currencies. GBP, USD, AED etc. Because the suppliers are from different parts of the world.

I am live calculating this data into a single currency AED on the front end of the site using live currency rates. So that prices do not need to be updated every time there are currency changes.

Here is my sorting query example...

/**
 * product archive filters
 * @return array
 */
public static function filters() {

    // determine our sorting filters
    switch(self::$sorting) {

        case 'price_highest':
            $filters['meta_key']   = 'product_price_from';
            $filters['orderby']    = 'meta_value_num';
            $filters['order']      = 'DESC';
            break;

        case 'price_lowest':
            $filters['meta_key']   = 'product_price_from';
            $filters['orderby']    = 'meta_value_num';
            $filters['order']      = 'ASC';
            break;

        case 'latest':
        default:
            $filters['orderby']    = 'date';
            $filters['order']      = 'DESC';
            break;

    }

    return $filters;

}

Because the number values all vary depending on currency rate, the orderby price results are not correct.

My question is there any kind of meta_key filter that I can apply to fetched value before being applied to the query.

For example I would need to apply a function like this... toAED($post_id,$value,$currency); to product_price_from meta value in the query.

Is this possible with Wordpress query?

Answer 1

So if i understand you correctly, you want to sort by a value that has to be calculated based on the currency? You can do this, but not within a wordpress wp_query of sorts.

Instead you can do one of two things:

Method 1: The "do more now, have less hassle later"-way: On saving the currency and price in your save_post action, you calculate the AED-price and save it in an extra meta field. When you need to sort by price, you sort by the AED-price. You will have to save all existing prices again into the new meta_field, but you can do this easily with a function that is called once and then never again.

Method 2: The "I don't care about db efficiency, i don't want hassle"-way: You have to write your conversion-function into the sql-query. As i don't know what exactly your conversion function does, i can only point you into the right direction. You need to add a filter to posts_clauses. In this filter function, you can manipulate the complete sql query parts, so you have to be very careful and implement checks so that you only change the query that you want changed, and not let's say the query for your main menu. You can read about this filter here: post_clauses.

Happy Coding!