Rails 4: Always use a Postgres function for a certain model field

Question

I've got a model Location with a field name that often contains diacritics - for example "Rhône-Alpes", "Midi-Pyrénées" and "Île-de-France".

This model gets used in a few different ways in my application, particularly for queries by the name. Examples include:

Location.find_by(name: 'Rhône-Alpes')
Location.find_or_create_by(name: 'Rhône-Alpes', country: 'France')

The arguments to those find methods vary across the application. My question is this: how can I make sure that a certain Postgres function (in this case unaccent()) is called every time the name field is referred to? This means I can search for 'Rhone' and 'Rhône', and get the same results.

Is it best to override the find_by and find_or_create_by methods? If so, what's the best way of doing this?

Answer 1

It's always good to push logic down into the database layer, but if you work on lots of different projects all the time, you might need to perform a find without unaccenting in future, then run into problems when you can't easily debug so you must always be careful when doing so.

Do not override the find_by method, this isn't recommended as you may perform a standard find_by and have unforeseen consequences.

Have you considered sanitising the input before the find_by?

>> ActiveSupport::Inflector.transliterate("Rhône-Alpes")
=> "Rhone-Alpes"

Answer 2

I know you're using find_by and find_or_create_by when querying by name, and you could override those class methods, not exactly sure how to do it the right way, but it should be pretty simple. But why not creating a class method for specifically querying by name?, like a scope!. There you could transform both the given name as a parameter, and the actual name value in the model. Something like this (assuming you have a Ruby method unaccent at your disposal)...

def self.named(name)
  self.all.select {|location| unaccent(location.name) == unaccent(name) }
end

Not sure if it is the answer you're looking for, but it is a way to do it, and you leave this logic in the model layer, independently of the DBMS you're using.

Best.