2

I am using wordpress REST API to query the database. For each product (a custom post) I have a meta which is product_price at a certain currency currency_rate. I need to run a query to get all products ordered ASC or DESC depending on product_price . However, since each price is at a different currency, the query needs first to convert all prices to a common currency, then sort them by the chosen order. I am not able to do it. I found this link wordpress orderby filter but I couldn't understand how to use it. The documentation is not clear at all for me. I need to modify the ordering by something similar to the below

$args = array(
        'post_type' => 'product',
        'meta_key' => 'product_price',
        'orderby'   => 'meta_value_num',
        'order' => 'ASC'
        );
$q = new WP_Query($args);

add_filter( 'posts_orderby', 'filter_query',10,2 );

function filter_query($orderby_statement, $wp_query) {
    $orderby_new_statement = 'product_price*currency_rate ASC';
    return $orderby_new_statement;
}

What I need to do is to multiply each price by a conversion rate and feed that into the query to give me the posts in the correct order. I am currently stuck. Thanks for your help.

1

So I think there's two solutions to this.

  1. Precalculate this value. I would suggest looking in to this if you can. Maybe hook into the post save hook and do a simple operation to save calculated price in a new meta value. This saves the below complicated code.

  2. It is possible to do this with WP_Query exactly as you said, and here's how. This tries to do as much as possiible inside the way WP_Query does things and then does a small hack to make a custom ORDER BY clause exactly like you suggested which is otherwise impossible to achieve with WP Query args alone.

WP Query Args

First, make sure WP Query joins to the post meta table twice and that we have the two meta values required:

$args = array(
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key' => 'currency_rate',
            'compare' => 'EXISTS',
        ),
        array(
            'key' => 'product_price',
            'compare' => 'EXISTS',
        ),
    ),
);

This causes WP_Query to setup a query that looks like this:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  
INNER JOIN wp_postmeta AS mt1 ON ( wpposts.ID = mt1.post_id )

So we have the two values we need in joins to wp_postmeta and mt1

Filter Order By

Therefore, the ORDER BY is easy to write as it's the meta_value key from these two tables multiplied:

function orderByPriceTimesCurrency($orderby, $wpq) {
    global $wpdb;
    return $wpdb->prefix . "postmeta.meta_value * mt1.meta_value ASC";
}

add_filter('posts_orderby_request', 'orderByPriceTimesCurrency', 10, 2);

I tested this and it worked great for me, however I was using different names for the variables, so let me know if you have any problems.

Important Notes

Some important points about using this:

  1. It relies on understanding the inner workings of WP_Query, particularly the way WP_Query joins to the meta table. If the implementation of WP_Query changes, this might break.
  2. It will only find posts that have both meta values set
  3. To avoid complications this completely overwrites the ORDER BY, so in this query you can't specify any other WP_Query order parameters. You either have to add them manually to the filter, or change the filter so that it appends the order statements somehow instead of replacing them.

All Pieces Together

$args = array(
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key' => 'currency_rate',
            'compare' => 'EXISTS',
        ),
        array(
            'key' => 'product_price',
            'compare' => 'EXISTS',
        ),
    ),
);

function orderByPriceTimesCurrency($orderby, $wpq) {
    global $wpdb;
    return $wpdb->prefix . "postmeta.meta_value * mt1.meta_value ASC";
}

add_filter('posts_orderby_request', 'orderByPriceTimesCurrency', 10, 2);

$q = WP_Query($args);

remove_filter('posts_orderby_request', 'orderByPriceTimesCurrency');
| improve this answer | |
  • Thanks a lot for the quick reply. I implemented solution 1 but I prefer solution 2 which I plan to try and revert back to you. – houssameddin Jul 16 at 11:46
  • 1
    Cool - if this answer was useful to you upvotes and accepts are appreciated, or please do add another answer to your question with the implementation for option 1 so others can use it – mozboz Jul 16 at 12:52

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.