The code in this article is a customisation. As such, it's provided as is, without implicit or explicit warranties, and it's outside the scope of our support service. Should you need assistance implementing the code, please feel free to contact us to request a consultation.


The price filtering logic built in WooCommerce relies on a query that loads data directly from the database to find the products to include. Such logic works fine in a single-currency scenario, as each product only has one price, which doesn't change. This filtering system, although fairly efficient, might not be as accurate in a multi-currency scenario.

In a multi-currency shop, product prices could be calculated on the fly, using exchange rates that can vary frequently, and such prices are not stored in the database. Due to that, the filtering has to be applied after converting the minimum and maximum values entered in the filter from the active currency, in which they were entered, to the shop's base currency, in which product prices are stored. For example, if your shop base currency is USD, and the customer filters product priced between 100 and 500 EUR, the actual filtering is performed using values like 117 and 588 USD (sample conversion rates, taken in November 2017) against the product prices in USD.


This approach allows to keep the existing filtering logic, as well as retain its performance, but it has two shortcomings:

  1. It might not be always accurate. Prices in additional currencies may vary greatly from their base currency equivalent, thus preventing the filter from showing all the products.
  2. If the prices in shop's base currency are left empty for some products, the query won't find such products, regardless of the filter applied. 


How to make the filtering more accurate, taking multi-currency prices into account

To improve the accuracy of the filtering, it's necessary to sacrifice the performance in favour of accuracy, by selecting the products after calculating their actual price on the fly.


Below you can find some custom code that intercepts the filtering logic, calculates the price of each product on the fly, and includes them in the result.


Important

The efficiency and performance of this solution depends on how many products are in the catalogue, and on how many elements, besides the currency conversion, alter the product prices during the calculation. You can expect the filtering performed using dynamically calculated prices to be significantly slower than the native one, due to the additional checks and calculations required.



Example 1 - How to filter products with dynamic multi-currency pricing

/**
 * Filter the posts returned by the main product query, to ensure that only the
 * ones whose price falls into the price range are displayed.
 *
 * IMPORTANT
 * This code can be slow, because the price of each product is calculated on the
 * fly. Such operation can involve currency conversion calculations, as well as
 * loading of product prices specific to current customer's country.
 *
 * @param array product_ids
 * @return array
 */
add_filter('loop_shop_post_in', function($product_ids) {
  static $shop_base_currency = null;

  // If no price filtering was applied, return the original list
  if(!isset($_GET['min_price']) && !isset($_GET['max_price'])) {
    return $product_ids;
  }
  
  // Fetch and cache the shop's base currency
  if($shop_base_currency === null) {
    $shop_base_currency = get_option('woocommerce_currency');
  }

  // If we're filtering by the shop's base currency, don't do anything and use the
  // default logic
  if(get_woocommerce_currency() === $shop_base_currency) {
    return $product_ids;
  }

  $min_price = !empty($_GET['min_price']) ? $_GET['min_price'] : 0;
  $max_price = !empty($_GET['max_price']) ? $_GET['max_price'] : 0;

  // Prepare the ID of a transient where we will stored the products that fall
  // within the price range. The call to WC_Cache_Helper::get_transient_version()
  // will get an ID that automatically includes the active currency (thanks to
  // the Aelia Currency Switcher) and customer's country (thanks to the Prices by
  // Country plugin)
  $transient_id = implode('_', array(
    'wc_aelia_price_filter_hash',
    (string)$min_price,
		(string)$max_price,
		get_woocommerce_currency(),
    WC_Cache_Helper::get_transient_version('product')
  ));
  // If the transient is set, we can take it. It's much faster than running a
  // potentially intensive query
  $product_ids = get_transient($transient_id);

  if($product_ids === false) {
    // POSSIBLE IMPROVEMENTS
    // Eventually, alter the parameters to exclude products that would be hidden
    // regardless of the price, e.g. hidden products, out of stock products, etc.
    $args = array(
      'posts_per_page' => -1,
      'post_type' => 'product',
      // This is important if you use WPML. The filters will ensure that the
      // product IDs returned by the query will exist in current context and
      // language
      'suppress_filters' => true,
    );
    $posts = get_posts($args);

    $product_ids = array();
    foreach($posts as $entry) {
      // Skip any product that might already be in the list
      if(in_array($entry->ID, $product_ids)) {
        continue;
      }

      // Load the product
      $product = wc_get_product($entry->ID);

      // Get the price on the fly. This will return the price in the active currency,
      // converted automatically if needed.
      // If the Prices by Country plugin is installed, the returned price will be
      // the one applicable to customer's country
      $product_price = $product->get_price();
      if($product_price >= $min_price && $product_price <= $max_price) {
        $product_ids[] = $entry->ID;
      }
    }

    // Store the resulting product IDs in the transient for one hour. That will
    // reduce the load on the server, which won't have to run again the query
    // to find the products falling in the price range
    set_transient($transient_id, $product_ids, HOUR_IN_SECONDS);
	}

	// Remove the "min_price" and "max_price" temporarily, so that WooCommerce won't
	// apply the price filter clause to the query.
	//
	// @see WC_Query::price_filter_post_clauses()
	add_filter('posts_clauses', function($clauses) {
		$_GET['__min_price'] = $_GET['min_price'];
		$_GET['__max_price'] = $_GET['max_price'];
		unset($_GET['min_price']);
		unset($_GET['max_price']);

		return $clauses;
	}, 5);

	// Restore the "min_price" and "max_price" after WooCommerce processed the query.
	//
	// @see WC_Query::price_filter_post_clauses()
	add_filter('posts_clauses', function($clauses) {
		$_GET['min_price'] = $_GET['__min_price'];
		$_GET['max_price'] = $_GET['__max_price'];

		unset($_GET['__min_price']);
		unset($_GET['__max_price']);

		return $clauses;
	}, 55);

  return $product_ids;
}, 10, 1);


/**
 * Returns the prices of all the products in the catalogue.
 *
 * @param boolean $numeric_only
 * @return array
 */
function aelia_get_all_product_prices($numeric_only = true) {
  $transient_id = implode('_', array(
    'wc_aelia_get_all_product_prices',
    get_woocommerce_currency(),
    WC_Cache_Helper::get_transient_version('product')
	));

	$product_prices = get_transient($transient_id);
	if($product_prices === false) {
		$product_prices = aelia_fetch_product_prices();
		if(is_array($product_prices)) {
			// Cache the products, for performance
			set_transient($transient_id, $product_prices, HOUR_IN_SECONDS);
		}
	}

	return $numeric_only ? array_filter($product_prices, 'is_numeric') : $product_prices;
}

/**
 * Fetches the prices of all the products in the catalogue.
 *
 * @return array
 * @see WC_Widget_Price_Filter::get_filtered_price()
 */
function aelia_fetch_product_prices() {
	global $wpdb;

	$args       = WC()->query->get_main_query()->query_vars;
	$tax_query  = isset( $args['tax_query'] ) ? $args['tax_query'] : array();
	$meta_query = isset( $args['meta_query'] ) ? $args['meta_query'] : array();

	if ( ! is_post_type_archive( 'product' ) && ! empty( $args['taxonomy'] ) && ! empty( $args['term'] ) ) {
		$tax_query[] = WC()->query->get_main_tax_query();
	}

	foreach ( $meta_query + $tax_query as $key => $query ) {
		if ( ! empty( $query['price_filter'] ) || ! empty( $query['rating_filter'] ) ) {
			unset( $meta_query[ $key ] );
		}
	}

	$meta_query = new WP_Meta_Query( $meta_query );
	$tax_query  = new WP_Tax_Query( $tax_query );
	$search     = WC_Query::get_main_search_query_sql();

	$meta_query_sql   = $meta_query->get_sql( 'post', $wpdb->posts, 'ID' );
	$tax_query_sql    = $tax_query->get_sql( $wpdb->posts, 'ID' );
	$search_query_sql = $search ? ' AND ' . $search : '';

	$sql = "
		SELECT DISTINCT product_id
		FROM {$wpdb->wc_product_meta_lookup}
		WHERE product_id IN (
			SELECT ID FROM {$wpdb->posts}
			" . $tax_query_sql['join'] . $meta_query_sql['join'] . "
			WHERE {$wpdb->posts}.post_type IN ('" . implode( "','", array_map( 'esc_sql', apply_filters( 'woocommerce_price_filter_post_type', array( 'product' ) ) ) ) . "')
			AND {$wpdb->posts}.post_status = 'publish'
			" . $tax_query_sql['where'] . $meta_query_sql['where'] . $search_query_sql . '
		)';

	$prices = array();
	foreach(wp_list_pluck($wpdb->get_results( $sql ), 'product_id') as $product_id) {
		$product = wc_get_product($product_id);
		$prices[$product_id] = $product->get_price();
	}
	return $prices;
}

/**
 * Sets the minimum value of the range, in the price filter widget.
 */
add_filter('woocommerce_price_filter_widget_min_amount', function($min_amount) {
	return min(aelia_get_all_product_prices());
});

/**
 * Sets the maximum value of the range, in the price filter widget.
 */
add_filter('woocommerce_price_filter_widget_max_amount', function($min_amount) {
	return max(aelia_get_all_product_prices());
});

You can purchase the Currency Switcher from our online shop.