0

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?

3

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!

| improve this answer | |
  • Thanks for your insight. This was as I feared. My prices_from field is actually created using save_post action. I might have to just create another one for AED like you said just for ease. Then create a function to update that field when currency a rate is updated. I'm not really too keen of the sql query idea as i've alot of params and filters already setup using wp_query. – joshmoto Jan 14 at 15:16
  • Yeah, additionally to the complex sql stuff you would have to write it would put additional strain to your database. Better to write one function now to update all posts to calculate the aed-price ;) If my post solved your problem, please check the little checkmark on the left side to mark your problem answered. – HU ist Sebastian Jan 14 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.