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:

Product Price Converted
A 10,00 EUR 13,51 USD
B 12,00 USD 8,88 EUR

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

The following two tabs change content below.
Seine erste Webseite hat David Remer 1998 in HTML verfasst. Wenig später war er fasziniert von DHTML und JavaScript. Heute konzentriert sich vor allem auf das Entwickeln von WordPress Themes und Plugins für Inpsyde. Außerdem hat er das Buch "WordPress für Entwickler" verfasst.

Latest posts by David Remer (see all)