WordPress Order by Price with two currencies

A client of mine runs a WordPress site where user can add products. The prices are stored as a postmeta value. In a second postmeta it is saved, in which currency the price is saved. So the user cann add the price as US-Dollar or Euro.

The client now asked if it is possible to order the list by price ascending and price descending. So usually, we would alter our WP_Query like this:

$args = array(
	'post_type'	=> 'products',
	'meta_key'	=> 'price',
	'orderby'	=> 'meta_value_num',
	'order'		=> 'ASC'
);
$query = new WP_Query( $args );

But in this particular case, the meta_value field was not consistent. This approach would lead to problems. Lets say, you have the following products and prices:

So, product B would be listed under product A also it is cheaper!

This site already converts one currency in the other to display it. We store the exchange rate in the option ‘currency_rate’ which is updated on a daily basis. What we need now is a different approach to order our search results in a proper way. For this, we need to use some filter hooks.

In the PHP-file which will execute the WP_Query we added these lines just before we execute the WP_Query:

if( isset( $_GET['order'] ) && ( $_GET['order'] == 'asc' || $_GET['order'] == 'desc' ) ):		
	add_filter('posts_join_paged','orderbyprice_posts_join_paged');
	add_filter('posts_where_request','orderbyprice_posts_where_request');		
	add_filter('posts_orderby','orderbyprice');
endif;

In the query.php of WordPress (wp-includes/query.php), we will find these filters in the Lines
2860 – 2866. With these filters, we can alter the actual MySQL-Query. We can add new tables, WHERE-conditions and modify the actual ORDER BY statement.

So, we created this solution:

prefix . "postmeta ON ( " . $wpdb->prefix . "postmeta.post_id = " . $wpdb->prefix . "posts.ID) ";
	$join_paged_statement .= " LEFT JOIN " . $wpdb->prefix . "postmeta as currency ON (currency.post_id = " . $wpdb->prefix . "posts.ID) ";
	return $join_paged_statement;	
}

function orderbyprice_posts_where_request( $where ){
	global $wpdb;
	$where .= " AND ( " . $wpdb->prefix . "postmeta.meta_key = 'price' ) AND ( currency.meta_key = 'currency' ) ";
	return $where;
}

function orderbyprice( $orderby ){
	global $wpdb;
	$rate = get_option( 'currency_rate' );
	if( $_SESSION['currency'] == '$' ):
		$sql = "IF ( currency.meta_value != '$', ( CAST(" . $wpdb->prefix . "postmeta.meta_value as DECIMAL(10,2)) / " . $rate . ") , CAST(" . $wpdb->prefix . "postmeta.meta_value as DECIMAL(10,2)) ) " . $_GET['order']  . " ";
	else:
		$sql = "IF ( currency.meta_value = '$', ( CAST(" . $wpdb->prefix . "postmeta.meta_value as DECIMAL(10,2)) * " . $rate . ") , CAST(" . $wpdb->prefix . "postmeta.meta_value as DECIMAL(10,2)) ) " . $_GET['order']  . " ";
	endif;
	return $sql;
}
?>

So, first we add two postmeta-tables in orderbyprice_posts_join_paged() and define them as containing the currency and the price in orderbyprice_posts_where_request()

In our function orderbyprice() we first get the exchange rate. Depending on the currency of the session we will return two different SQL statements. Both statements contain an IF-clause, which you can use in MySQL.

So: If the current session is $ and the currency-postmeta is not $ we return the price/rate. If the currency-postmeta is $, we just return the price. We have to convert the meta_value field with CAST, so MySQL uses the value as an float and not as a string. And thats it!

Photo Credit: Chobist cc