在Heroku上的Rails 3.1中对Postgres强调不敏感的LIKE搜索 [英] Postgres accent insensitive LIKE search in Rails 3.1 on Heroku
问题描述
如何在Rails中修改搜索查询的where / like条件:
How can I modify a where/like condition on a search query in Rails:
find(:all,:conditions => [ lower(name)Like?,%#{search.downcase}%])
,以便匹配结果不论口音如何? (例如,地铁=地铁)。因为我使用的是utf8,所以不能使用 to_ascii。生产正在Heroku上进行。
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.
推荐答案
穷人的解决方案
如果您可以创建一个功能,就可以使用此功能。我从此处开始编译列表,并随着时间的推移添加到列表中。它很完整。您甚至可能希望删除某些字符:
Poor man's solution
If you are able to create a function, you can use this one. I compiled the list starting here and 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($1
, '¹²³áàâãäåāăąÀÁÂÃÄÅĀĂĄÆćčç©ĆČÇĐÐèéêёëēĕėęěÈÊËЁĒĔĖĘĚ€ğĞıìíîïìĩīĭÌÍÎÏЇÌĨĪĬłŁńňñŃŇÑòóôõöōŏőøÒÓÔÕÖŌŎŐØŒř®ŘšşșߊŞȘùúûüũūŭůÙÚÛÜŨŪŬŮýÿÝŸžżźŽŻŹ'
, '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 very fast results:
CREATE INDEX tbl_name_lower_unaccent_idx
ON fest (lower_unaccent(name) text_pattern_ops);
对于以下查询:
SELECT * FROM tbl WHERE (lower_unaccent(name)) ~~ 'bob%'
正确的解决方案
在 PostgreSQL 9.1 + 中,具有必要的特权,您可以:
Proper solution
In PostgreSQL 9.1+, with the necessary privileges, you can just:
CREATE EXTENSION unaccent;
它提供了 unaccent()$ c $函数c>
,执行您需要的操作( lower()
除外,如果需要,只需额外使用即可)。阅读有关此扩展程序的手册。
也可用于 PostgreSQL 9.0 ,但创建扩展
语法是9.1中的新增功能。
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.0 but CREATE EXTENSION
syntax is new in 9.1.
更多关于不重音和索引:
More about unaccent and indexes:
- Does PostgreSQL support "accent insensitive" collations?
这篇关于在Heroku上的Rails 3.1中对Postgres强调不敏感的LIKE搜索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!