9

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.

Community
  • 1
  • 1
snowguy
  • 860
  • 2
  • 12
  • 21
  • possible duplicate of [Case-insensitive search in Rails model](http://stackoverflow.com/questions/2220423/case-insensitive-search-in-rails-model) – Brad Werth Sep 19 '14 at 19:59

4 Answers4

33

With PostgreSQL you can do:

User.where("email ILIKE ?", email).first
tomaszbak
  • 7,839
  • 4
  • 42
  • 36
  • is this vulnerable to SQL injection? – GMA May 02 '14 at 07:54
  • 5
    To be clear (for future readers), the version in the question is safe from SQL injection. The version in the comment isn't. – Kevin Jun 11 '14 at 23:28
  • @Kevin the version of what, Rails? PostgreSQL? – evanrmurphy Feb 16 '16 at 17:22
  • @evanrmurphy It looks like there was an old comment that has since been deleted that I was referring to. – Kevin Mar 18 '16 at 20:13
  • 2
    `User.find_by 'email ILIKE ?', email` is how Rubocop recommends this, using [`find_by`](http://api.rubyonrails.org/classes/ActiveRecord/FinderMethods.html#method-i-find_by) instead of where.first. – Brian Sigafoos Apr 28 '18 at 15:09
19

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.

Dimitri
  • 1,823
  • 15
  • 20
Matzi
  • 13,145
  • 3
  • 32
  • 48
  • Thanks Matzi, I like your suggestions and I'm going to implement the first one. Just curious would you recommend creating this new method rather than overriding find_by_email? To me overriding it seems nicer since it is natural to call find_by_email--but then I'm not sure how. – snowguy Jul 05 '12 at 16:46
  • In rails 2.0 it was flawed, and when you called super, the missing_method overridden your find_by function, causing it worked only once. I don't know if it is corrected, but better to be safe than sorry. If you want to call find_by_email, then use my second option, that should work anyway. – Matzi Jul 05 '12 at 17:07
  • Sorry, not following the last part you wrote "if you want to call find_by_email, then use my second option ...". In your second option aren't I still calling find_by_lower_email? – snowguy Jul 05 '12 at 17:53
  • I mean the two parts of my function are two different implementations. The second one can be used if you call the function find_by_email. – Matzi Jul 05 '12 at 17:55
  • awesome. thanks for the revision. i get it. I'm going to implement the second solution. – snowguy Jul 05 '12 at 18:07
  • just remember for the second method offered: find_by_email returns one result normally, so you will need to call .first on the overwritten methods' array response if you want it to behave normally. – Danny Jun 29 '13 at 20:24
12

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
rylanb
  • 568
  • 4
  • 15
  • either I am missing your point or you missed the point of my question. I agree that typically you should downcase before saving. If you assume that is done, you don't need the "lower(email) =?" part but you would still need the email.downcase part--since often the email address is supplied by the user and they may not have downcased--that was the point of my question. I believe Matzi's response is correct. – snowguy Aug 16 '12 at 06:12
  • Well you should know if it is or is not downcased on insertion into the DB. It seems that Matzi's response is a great solution, but its making assumptions that the email is lowercased in the DB or is lowercased when passed into the find_by_email method, I believe. His also will return an array of emails, while :first will return the first response as the actual Member object. No? – rylanb Aug 16 '12 at 20:16
  • you make a good point on :first. I don't think it is wrong though to assume that the e-mail is downcased in the solution since this is specifically noted in the question. – snowguy Aug 26 '12 at 04:30
0

it works for me! thank you, but with lower(:xxx).

Client.includes(:responsibles).where(["lower(name) || lower(contact) || cnpj || clients.rg || rg ILIKE ?", "%#{params[:search]}%"])

Vitor Rocha
  • 21
  • 1