postgresql Heroku 上 Rails 3.1 中的 Postgres 重音不敏感 LIKE 搜索
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9243322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres accent insensitive LIKE search in Rails 3.1 on Heroku
提问by user1051849
How can I modify a where/like condition on a search query in Rails:
如何修改 Rails 中搜索查询的 where/like 条件：
find(:all, :conditions => ["lower(name) LIKE ?", "%#{search.downcase}%"])
find(:all, :conditions => ["lower(name) LIKE ?", "%#{search.downcase}%"])
so that the results are matched irrespective of accents? (eg métro = metro). Because I'm using utf8, I can't use "to_ascii". Production is running on Heroku.
这样无论口音如何，结果都匹配？（例如地铁 = 地铁）。因为我使用的是 utf8，所以我不能使用“to_ascii”。生产正在 Heroku 上运行。
回答by Erwin Brandstetter
Poor man's solution
穷人的解决办法
If you are able to create a function, you can use this one. I compiled the list starting hereand added to it over time. It is pretty complete. You may even want to remove some characters:
如果您能够创建函数，则可以使用此函数。我从这里开始编译列表并随着时间的推移添加到它。它非常完整。您甚至可能想要删除一些字符：
CREATE OR REPLACE FUNCTION lower_unaccent(text)
RETURNS text AS
$func$
SELECT lower(translate(
, '123áàa???ā??àá????ā???????????Dèéêё?ē???ěèê?Ёē???ě???ìí??ì?ī?ìí???ì?ī???ńň????òó???ō???òó???ō??????????????ùú?ü?ū??ùú?ü?ū??y?Y???????'
, '123aaaaaaaaaaaaaaaaaaacccccccddeeeeeeeeeeeeeeeeeeeeggiiiiiiiiiiiiiiiiiillnnnnnnooooooooooooooooooorrrsssssssuuuuuuuuuuuuuuuuyyyyzzzzzz'
));
$func$ LANGUAGE sql IMMUTABLE;
Your query should work like that:
您的查询应该像这样工作：
find(:all, :conditions => ["lower_unaccent(name) LIKE ?", "%#{search.downcase}%"])
For left-anchored searches, you can utilize an index on the function for veryfast results:
对于左锚搜索，您可以利用函数上的索引来获得非常快的结果：
CREATE INDEX tbl_name_lower_unaccent_idx
ON fest (lower_unaccent(name) text_pattern_ops);
For queries like:
对于像这样的查询：
SELECT * FROM tbl WHERE (lower_unaccent(name)) ~~ 'bob%'
Proper solution
正确的解决方案
In PostgreSQL 9.1+, with the necessary privileges, you can just:
在PostgreSQL 9.1+ 中，使用必要的权限，您可以：
CREATE EXTENSION unaccent;
which provides a function unaccent(), doing what you need (except for lower(), just use that additionally if needed). Read the manual about this extension.
Also available for PostgreSQL 9.0but CREATE EXTENSIONsyntax is new in 9.1.
它提供了一个功能unaccent()，做你需要的（除了lower()，如果需要，只需额外使用）。阅读有关此扩展的手册。
也可用于PostgreSQL 9.0，但CREATE EXTENSION语法是 9.1 中的新语法。
More about unaccent and indexes:
更多关于 unaccent 和索引：
回答by Edison Machado
For those like me who are having trouble on add the unaccentextension for PostgreSQL and get it working with the Rails application, here is the migration you need to create:
对于像我这样在unaccent为 PostgreSQL添加扩展并使其与 Rails 应用程序一起工作时遇到问题的人，这里是您需要创建的迁移：
class AddUnaccentExtension < ActiveRecord::Migration
def up
execute "create extension unaccent"
end
def down
execute "drop extension unaccent"
end
end
And, of course, after rake db:migrateyou will be able to use the unaccentfunction in your queries: unaccent(column) similar to ...or unaccent(lower(column)) ...
而且，当然，在rake db:migrate您能够unaccent在查询中使用该函数之后：unaccent(column) similar to ...或unaccent(lower(column)) ...
回答by Ruby Racer
First of all, you install postgresql-contrib. Then you connect to your DB and execute:
首先，您安装 postgresql-contrib。然后连接到数据库并执行：
CREATE EXTENSION unaccent;
to enable the extension for your DB.
为您的数据库启用扩展。
Depending on your language, you might need to create a new rule file (in my case greek.rules, located in /usr/share/postgresql/9.1/tsearch_data), or just append to the existing unaccent.rules(quite straightforward).
根据您的语言，您可能需要创建一个新的规则文件（在我的例子中greek.rules，位于/usr/share/postgresql/9.1/tsearch_data），或者只是附加到现有的unaccent.rules（非常简单）。
In case you create your own .rulesfile, you need to make it default:
如果您创建自己的.rules文件，则需要将其设为默认值：
ALTER TEXT SEARCH DICTIONARY unaccent (RULES='greek');
This change is persistent, so you need not redo it.
此更改是持久的，因此您无需重做。
The next step would be to add a method to a model to make use of this function.
下一步是向模型添加方法以使用此功能。
One simple solution would be defining a function in the model. For instance:
一个简单的解决方案是在模型中定义一个函数。例如：
class Model < ActiveRecord::Base
[...]
def self.unaccent(column,value)
a=self.where('unaccent(?) LIKE ?', column, "%value%")
a
end
[...]
end
Then, I can simply invoke:
然后，我可以简单地调用：
Model.unaccent("name","text")
Invoking the same command without the model definition would be as plain as:
在没有模型定义的情况下调用相同的命令将非常简单：
Model.where('unaccent(name) LIKE ?', "%text%"
Note: The above example has been tested and works for postgres9.1, Rails 4.0, Ruby 2.0.
注意：以上示例已经过测试，适用于 postgres9.1、Rails 4.0、Ruby 2.0。
UPDATE INFO
Fixed potential SQLi backdoor thanks to @Henrik N's feedback
更新信息
由于@Henrik N 的反馈，修复了潜在的 SQLi 后门
回答by Pierre
There are 2 questions related to your search on the StackExchange: https://serverfault.com/questions/266373/postgresql-accent-diacritic-insensitive-search
有 2 个问题与您在 StackExchange 上的搜索相关：https://serverfault.com/questions/266373/postgresql-accent-diacritic-insensitive-search
But as you are on Heroku, I doubt this is a good match (unless you have a dedicated database plan).
但是当你在 Heroku 上时，我怀疑这是一个很好的匹配（除非你有一个专门的数据库计划）。
There is also this one on SO: Removing accents/diacritics from string while preserving other special chars.
在 SO 上也有一个：Removing Accents/diacritics from string 同时保留其他特殊字符。
But this assumes that your data is stored without any accent.
但这假设您的数据存储时没有任何重音。
I hope it will point you in the right direction.
我希望它会为您指明正确的方向。
回答by Christian Fazzini
Assuming Foois the model you are searching against and nameis the column. Combining Postgres translateand ActiveSupport's transliterate. You can do something like:
假设Foo是您正在搜索的模型并且name是列。结合 Postgres translate和 ActiveSupport 的transliterate。您可以执行以下操作：
Foo.where(
"translate(
LOWER(name),
'a???ā??á????ā??èééê?ē???ěē???ěìí??ì?ī?ìí??ì?ī?ó???ō??òó???ō??ùú?ü?ū??ùú?ü?ū??',
'aaaaaaaaaaaaaaaeeeeeeeeeeeeeeeiiiiiiiiiiiiiiiiooooooooooooooouuuuuuuuuuuuuuuu'
)
LIKE ?", "%#{ActiveSupport::Inflector.transliterate("%qué%").downcase}%"
)
