搜索给定价格范围的不同货币的模型 [英] Search for a model given price range in different currencies

问题描述

场景：

产品以用户指定的货币显示，而每种产品都有自己的原始货币（可以相同

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)

用户想要以其用户指定的币种搜索特定价格范围（10-100）的产品（让我们假设是美元）

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

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

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)

推荐答案

您可以使用 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子句可以在任何地方使用表达式有效。每个
条件都是一个返回布尔结果的表达式。如果
条件的结果为true，则CASE表达式的值就是该条件之后的
结果，而CASE
表达式的其余部分将不处理。如果条件的结果不正确，则将以相同方式检查
后面的所有WHEN子句。如果没有
WHEN条件产生真，则CASE表达式的值是ELSE子句的
结果。如果省略ELSE子句并且没有
条件为真，则结果为null。

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)

这篇关于搜索给定价格范围的不同货币的模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！