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

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');