Rails Active Record case insensitive find

Question

Though in general I like case-sensitivity in my database, there are a few times I find it a pain. user_name and email are good examples of fields where I don't want to worry about the case--especially when searching.

It is easy enough to downcase a string before saving using something like:

before_save do self.email.downcase! end

This way the user_name and emails are always saved in lower case. But what is the best way to find_by_user_name and find_by_email? I can of course remember to always downcase the string I am passing into those methods--but that doesn't seem very DRY.

I thought about overriding find_by_email and calling the original with super once I downcased the e-amail, but I didn't have any luck figuring that out.

So, what is the best way to do this?

PS I don't think related posts like this one ( how can I write a case-insensitive find_by_email for Rails 3 ) are trying to solve for the same problem. Custom sql here with "lower" would be ointless for me since I already have ensured all of those values are lower--it is the one coming in right now (probably from a form where the user entered it) which I need downcased.

Answer 1

With PostgreSQL you can do:

User.where("email ILIKE ?", email).first

Answer 2

I did something like this:

def self.find_by_email(email)
  Member.find(:first, :conditions => ["lower(email) =?", email.downcase])
end

You can debate on if this is right or not, but generally email should PROBABLY be normalized going into the database to prevent the need to do this.

Edit: Rails 3:

Member.where("lower(email) =?", email.downcase).first

Answer 3

One of these should be good:

def self.find_by_lower_email(email)
  User.find_by_email(email.downcase)
end
# OR
def self.find_by_email(email)
  User.find(:all, :conditions => ["email = lower(?)", email]) 
end

Update:

The find_by_... functions are actually non-existing ones. When you call one of them, the ActiveRecord::Base catches your call in the method_missing method, and if the name is well formatted (contains column names, etc) then the appropriate find method is created for the class. After that it will exist.

If you have a find_by_email, it will be called by default. If it calls super, that means that you try to call ActiveRecord::Base.find_by_email, which does not exists. The missing_method catches it, and creates (actually overwrites your) find_by_email implementation. That is why not good to call super there. If you use your own implementation for finding by email, like I wrote in the second part, then it will work splendidly.