Ruby & Postgresql LIKE 用于字符串和小数

Question

我有一个在 sqllite 中工作的搜索查询，但是当我迁移到 postgres 时它不再工作了。我可以理解下面的错误，只是不确定如何修复它。这是错误:

ActiveRecord::StatementInvalid (PG::UndefinedFunction: ERROR:  operator does 
not exist: numeric ~~ unknown
LINE 1: ...ustomers".* FROM "customers" WHERE (credit_amount LIKE '%tri...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might 
need to add explicit type casts.

这是有道理的，我只是不确定该怎么做我查找了 CAST 以将我的小数转换为字符串，但我认为这是不对的。这是搜索:

search_term = "%#{params["search"]}%"
customers = Customer.where('credit_amount LIKE :search 
                      OR email LIKE :search 
                      OR first_name LIKE :search 
                      OR last_name LIKE :search
                      OR pending_credit_amount LIKE :search                          
                      OR tags LIKE :search', search: 
search_term).order(first_name: params["order"])

credit 和 pending_credit 是小数，其余是字符串。

谢谢

Answer 1

我认为问题在于 customers 表中的 credit_amount 是一个整数。如果是这样，您必须将 credit_amount 转换为文本，以便将其与搜索参数进行比较。我会这样做:

search_term = "%#{params["search"]}%"
customers = Customer.where('credit_amount::text LIKE :search 
                  OR email LIKE :search 
                  OR first_name LIKE :search 
                  OR last_name LIKE :search
                  OR pending_credit_amount LIKE :search                          
                  OR tags LIKE :search', 
                  search: search_term).order(first_name: params["order"])

与其他非文本列相同，例如我猜的“pending_credit_amount”列。