Order WP Query posts by custom order calculated from post meta values

Question

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.

mozboz · Answer

So I think there's two solutions to this.

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.

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:

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.
It will only find posts that have both meta values set
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');

Order WP Query posts by custom order calculated from post meta values

One Answer

WP Query Args

Filter Order By

Important Notes

All Pieces Together

Add your own answers!

Ask a Question