ruby-on-rails - 在不同货币的给定价格范围内搜索模型

Question

场景:

产品以用户指定的货币显示，而每个产品都有自己的原始货币(可以与用户指定的货币相同，可以是任何其他货币)

用户想要以用户指定的货币(假设为美元)搜索具有特定价格范围 (10 - 100) 的产品

我正在使用 postgres 和 Rails。每个产品都有原始货币的 price_amount 和 currency。

问题由于每个产品都可以使用任何货币，我需要将所有产品标准化为用户指定的货币，以便我可以看到哪个产品在范围内。

我有每对支持货币的汇率。但是查询会非常复杂，所以我想知道是否有更高效的方法？

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)

Answer 1

您可以使用 SQL CASE为此:

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 版本:

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)