使用%通配符的Rails SQL查询可在SQLite中使用,但不能在PostgreSQL中使用? [英] Rails SQL query with % Wildcards works in SQLite but not PostgreSQL?
问题描述
我有一个查询要使用:conditions这样的查询:
:conditions => [’family_name喜欢吗?或named_name喜欢吗?',%#{params [:search]}%,%#{params [:search]}%]
查询在SQLite上可以在本地正常运行,但是当我在PostgreSQL上推送到Heroku时,只有第一个%可以同时用于family_name和named_name。换句话说,它将匹配出现在单词结尾而不是开头或中间的关键字。
示例:
现有一条记录,其中包含:family_name => Washington和:given_name => George
将检索 ington或 rge该记录。
我是新用户,并且对Rails很陌生。
预先感谢您的帮助。
解决方案
这是我正在使用的解决方案:
:conditions => [’LOWER(family_name)喜欢吗?或LOWER(given_name)像?',%#{params [:search] .downcase}%,%#{params [:search] .downcase}%]
Like
在Postgres中区分大小写。对于不区分大小写的模式匹配,请使用 ILIKE
或其他标准方法。
I have a query I'm using for a search with :conditions like this:
:conditions => ['family_name LIKE ? OR given_name LIKE ?', "%#{params[:search]}%", "%#{params[:search]}%"]
The query works fine locally on SQLite, but when I push to Heroku on PostgreSQL, only the first % works for both family_name and given_name. In other words, it will match a keyword that occurs at the end of a word but not the beginning or middle.
Example: There is an existing record with :family_name => "Washington" and :given_name => "George"
A search for "ington" or "rge" will retrieve this record. A search for "Wash" or "Geo" will not.
I'm a new user and am pretty new to rails. Thanks in advance for your help.
Solution
Here's the fix I am using:
:conditions => ['LOWER(family_name) LIKE ? OR LOWER(given_name) LIKE ?', "%#{params[:search].downcase}%", "%#{params[:search].downcase}%"]
LIKE
is a case-sensitive operator in Postgres. For case-insensitive pattern matching use ILIKE
or other standard methods.
这篇关于使用%通配符的Rails SQL查询可在SQLite中使用,但不能在PostgreSQL中使用?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!