Find a price range by model in different currencies

advertisements

The scenario:

products are shown in a user-specified currency, while each product has its own original currency (could be same as user specified, could be any other currency)

a user wants to search for products with a specific price range (10 - 100) in their user-specified currency (let's assume USD)

I am using postgres and Rails. Each product has a price_amount and currency for the original currency.

The problem Since each product could be in any currency, I need to normalize all products to be in the user specified currency so I can see which product is in range.

I have the exchange rate for each pair of supported currencies. But the query will be really complex, so I wonder if there is something more efficient?

currencies.map do |currency|
 rate = ex_rate(user_specific_currency, currency)
 query = [query , "products.price * #{rate} <= user_max AND products.price * #{rate} >= user_min AND products.currency = '#{currency}'"].join(" OR ")
end

Product.where(query)


You can use SQL CASE for it:

SELECT price, currency,
       CASE WHEN currency='USD' THEN price * 1
            WHEN currency='RUB' THEN price * 65
            ELSE price
       END as final_price
FROM products
WHERE final_price BETWEEN 10 AND 100

CASE clauses can be used wherever an expression is valid. Each condition is an expression that returns a boolean result. If the condition's result is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed. If the condition's result is not true, any subsequent WHEN clauses are examined in the same manner. If no WHEN condition yields true, the value of the CASE expression is the result of the ELSE clause. If the ELSE clause is omitted and no condition is true, the result is null.

Rails version:

Product.select("price, currency, CASE
  WHEN currency='USD' THEN price * 1
  WHEN currency='RUB' THEN price * 65
  ELSE price END as final_price")
.where("final_price BETWEEN ? AND ?", 10, 100)