[ACCEPTED]-Rails 3. Validating email uniqueness and case sensitive fails-validation

Accepted answer

Score: 43

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

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

My suggesting is: Run a query to 4 make all the emails downcased and make a 3 before validation filter to downcase the 2 email attribute so you dont have any uppercased 1 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

Score: 32

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

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

Score: 3

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

validates :username, uniqueness: { case_sensitive: true }

ActiveRecord 9 performs this query:

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

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

validates :username, uniqueness: { case_sensitive: false }

it 7 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 6 for my setup at least, the case_sensitive 5 flag doesn't suffer from the performance 4 issue that @Michael Koper notes for earlier 3 versions of Rails. I can't comment on how 2 ActiveRecord performs for other database 1 setups.

Score: 1

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

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

validates_uniqueness_of :email, :case_sensitive => false

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

Score: 0

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

  before_validation { self.email = email.downcase }

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

validates :email, uniqueness: true

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

Source: stackoverflow.com

[ACCEPTED]-Rails 3. Validating email uniqueness and case sensitive fails-validation

Customize error message with simple_form

Rails 3 - check that object is valid with params before update

How to skip validations as admin during update_attributes?

How to implement min/max validator in Rails 3?

How do I validate start date and end date?

Rails 3: "field-with-errors" wrapper changes the page appearance. How to avoid this?

Prevent change of one field in Rails model

how to generate AuthenticityToken on rails

Can you do greater than comparison on a date in a Rails 3 search?

Rails 3 - Tempfile Path?