Rails 3. Validating email uniqueness and case sensitive fails

I am developing an app in Rails 3 and upon signup I need the user to enter their email address and I need it to be unique and case sensitive. I.e. no one should be able to sign up with myEmail@yahoo.com when MyEmail@yahoo.com already exists in the database.

This is my code and it crashes the app:

validates :email, :presence => true, :uniqueness => true, :case_sensitive => true,
                      :format => {:with => /^([^@\s]+)@((?:[-a-z0-9]+\.)+[a-z]{2,})$/i}

What is wrong wi开发者_开发百科th it?

Please dont use case sensitive there!!!. It will fetch all the users! So if you have 100.000 users. first it will fetch them all with LOWER(email). This can be VERY slow and it wont use your index on email.

Here an article that i found just now about this topic: http://techblog.floorplanner.com/post/20528527222/case-insensitive-validates-uniqueness-of-slowness

My suggesting is: Run a query to make all the emails downcased and make a before validation filter to downcase the email attribute so you dont have any uppercased characters in that column.

User.update_all('email = LOWER(email)')

before filter:

before_validation :downcase_email

private

def downcase_email
  self.email = email.downcase if email.present?
end

For the Rails 3 type of validation you need to nest the casse insensitive block like so

validates :email, :uniqueness => { :case_sensitive => false }

I don't have the reputation to comment on the accepted answer, but @medBo asked about how this behaves in Rails 4. For reference, when using Rails 4.2 + MySQL, if I specify

validates :username, uniqueness: { case_sensitive: true }

ActiveRecord performs this query:

SELECT  1 AS one FROM `users` WHERE `users`.`username` = 'TEST_USER' LIMIT 1

In this case the search is not case sensitive. But when I set:

validates :username, uniqueness: { case_sensitive: false }

it performs:

SELECT  1 AS one FROM `users` WHERE `users`.`username` = BINARY 'TEST_USER'

The BINARY operator ensures the search is case sensitive without fetching all users, meaning for my setup at least, the case_sensitive flag doesn't suffer from the performance issue that @Michael Koper notes for earlier versions of Rails. I can't comment on how ActiveRecord performs for other database setups.

I'm not sure if it is possible to do case insensitive validations using that syntax (at least, I haven't found any documentation for it).

You should be able to validate case insensitive uniqueness like this though:

validates_uniqueness_of :email, :case_sensitive => false

Without more detail on the crash you get, I can't help more than that.

You can use a callback in your model like "before_validation" on email attribute to make it lowercased like this:

  before_validation { self.email = email.downcase }

This will make the email input lowercased, after that try uniqueness validation without case sensitive:

validates :email, uniqueness: true

For more info about callbacks: here is ruby guide https://guides.rubyonrails.org/active_record_callbacks.html