Postgres accent insensible comme la recherche dans les Rails 3.1 sur Heroku
Comment puis-je modifier une condition where/like sur une requête de recherche dans les Rails:
find(:all, :conditions => ["lower(name) LIKE ?", "%#{search.downcase}%"])
de sorte que les résultats sont comparés indépendamment des accents? (par exemple, métro = métro). Parce que j'utilise utf8, Je ne peux pas utiliser "to_ascii". La Production tourne sur Heroku.
5 réponses
Pauvre homme solution
Si vous êtes en mesure de créer une fonction, vous pouvez utiliser ce un. J'ai compilé la liste de départ ici et ajoutés au fil du temps. Il est assez complet. Vous pouvez même supprimer certains caractères:
CREATE OR REPLACE FUNCTION lower_unaccent(text)
RETURNS text AS
$func$
SELECT lower(translate(
, '¹²³áàâãäåāăąÀÁÂÃÄÅĀĂĄÆćčç©ĆČÇĐÐèéêёëēĕėęěÈÊËЁĒĔĖĘĚ€ğĞıìíîïìĩīĭÌÍÎÏЇÌĨĪĬłŁńňñŃŇÑòóôõöōŏőøÒÓÔÕÖŌŎŐØŒř®ŘšşșߊŞȘùúûüũūŭůÙÚÛÜŨŪŬŮýÿÝŸžżźŽŻŹ'
, '123aaaaaaaaaaaaaaaaaaacccccccddeeeeeeeeeeeeeeeeeeeeggiiiiiiiiiiiiiiiiiillnnnnnnooooooooooooooooooorrrsssssssuuuuuuuuuuuuuuuuyyyyzzzzzz'
));
$func$ LANGUAGE sql IMMUTABLE;
Votre requête devrait fonctionner comme ça:
find(:all, :conditions => ["lower_unaccent(name) LIKE ?", "%#{search.downcase}%"])
pour les recherches ancrées à gauche, vous pouvez utiliser un index sur la fonction pour très résultats rapides:
CREATE INDEX tbl_name_lower_unaccent_idx
ON fest (lower_unaccent(name) text_pattern_ops);
Pour les requêtes comme:
SELECT * FROM tbl WHERE (lower_unaccent(name)) ~~ 'bob%'
solution appropriée
PostgreSQL 9.1+, avec les privilèges nécessaires, vous pouvez juste:
CREATE EXTENSION unaccent;
, qui fournit une fonction unaccent()
, faire ce que vous avez besoin (à l'exception de lower()
, il suffit d'utiliser cela en plus si nécessaire). Lire le manuel sur cette extension.
Aussi disponible pour PostgreSQL 9.0 mais CREATE EXTENSION
la syntaxe est nouveau dans 9.1.
More about uncent and indexes:
pour ceux comme moi qui ont des problèmes sur Ajouter le unaccent
extension de PostgreSQL et de le faire fonctionner avec l'application Rails, ici, c'est de la migration, vous devez créer:
class AddUnaccentExtension < ActiveRecord::Migration
def up
execute "create extension unaccent"
end
def down
execute "drop extension unaccent"
end
end
Et, bien sûr, après rake db:migrate
vous pourrez utiliser le unaccent
fonction dans vos requêtes: unaccent(column) similar to ...
ou unaccent(lower(column)) ...
tout d'abord, vous installez postgresql-contrib. Ensuite, vous vous connectez à votre DB et exécutez:
CREATE EXTENSION unaccent;
pour activer l'extension de votre DB.
selon votre langue, vous pourriez avoir besoin de créer un nouveau fichier de règles (dans mon cas greek.rules
, située en /usr/share/postgresql/9.1/tsearch_data
), ou tout simplement ajouter à l'existant unaccent.rules
(assez simple).
Dans le cas où vous créez votre propre .rules
le fichier, vous devez le faire par défaut:
ALTER TEXT SEARCH DICTIONARY unaccent (RULES='greek');
Ce changement est persistant, donc vous n'avez pas besoin de le refaire.
La prochaine étape serait d'ajouter une méthode à un modèle de faire usage de cette fonction.
une solution simple serait de définir une fonction dans le modèle. Par exemple:
class Model < ActiveRecord::Base
[...]
def self.unaccent(column,value)
a=self.where('unaccent(?) LIKE ?', column, "%value%")
a
end
[...]
end
Ensuite, il me suffit d'appeler:
Model.unaccent("name","text")
Invocation de la même commande sans la définition du modèle serait aussi simple que:
Model.where('unaccent(name) LIKE ?', "%text%"
Remarque: L'exemple ci-dessus a été testé et fonctionne pour postgres9.1, Rails 4.0, Ruby 2.0.
UPDATE INFO
Correction de la porte dérobée de SQLi grâce au feedback de @Henrik N
il y a 2 questions liées à votre recherche sur le StackExchange: https://serverfault.com/questions/266373/postgresql-accent-diacritic-insensitive-search
mais comme vous êtes sur Heroku, je doute que ce soit une bonne correspondance (à moins que vous ayez un plan de base de données dédiée).
il y a aussi celui-ci sur SO: supprimer les accents / diacritiques de la chaîne tout en préservant les autres caractères spéciaux.
Mais cela suppose que vos données sont stockées sans aucun accent.
j'espère que cela vous indiquera la bonne direction.
en Supposant que Foo
est le modèle que vous êtes à la recherche contre et name
est la colonne. Combinaison De Postgres traduire et ActiveSupport translittérer. Vous pouvez faire quelque chose comme:
Foo.where(
"translate(
LOWER(name),
'âãäåāăąÁÂÃÄÅĀĂĄèééêëēĕėęěĒĔĖĘĚìíîïìĩīĭÌÍÎÏÌĨĪĬóôõöōŏőÒÓÔÕÖŌŎŐùúûüũūŭůÙÚÛÜŨŪŬŮ',
'aaaaaaaaaaaaaaaeeeeeeeeeeeeeeeiiiiiiiiiiiiiiiiooooooooooooooouuuuuuuuuuuuuuuu'
)
LIKE ?", "%#{ActiveSupport::Inflector.transliterate("%qué%").downcase}%"
)