Replacing where(username: usernames) with case-insensitive finder

Question

I was previously using where(username: usernames) to take an array of usernames like and return an ActiveRecord::Relation of users. I've since created an Index on lower(username) and replaced User#find_by_username with the following:

def self.find_by_username(username)
  where("lower(username) = ?", username.downcase).first
end

This fails:

def self.find_by_usernames(usernames)
  where("lower(username) = ?", usernames.map(&:downcase))
end

It works when it's an array of one username, but not for multiple usernames. It generates SQL like "SELECT \"users\".* FROM \"users\" WHERE (lower(username) = 'joshsmith','vendiddy')"

score 3 · Accepted Answer · answered Apr 29 '14 at 17:51

3

Update your method as below:

def self.find_by_usernames(usernames)
  where("lower(username) in (?)", usernames.map(&:downcase))
end

Use in instead of = because you want to check against multiple values in an array.

answered Apr 29 '14 at 17:51

Kirti Thorat

50,170
10
97
103

Excellent! I had tried the parens and thought I had tried the `IN`, but guess I was doing something else wrong in the REPL. Thank you. – Josh Smith Apr 29 '14 at 17:53
Glad to help. Please accept the answer when timeout is over. :) – Kirti Thorat Apr 29 '14 at 17:54
Will do. Set a timer. :) – Josh Smith Apr 29 '14 at 17:55
hehe..surely :D Your time starts now. – Kirti Thorat Apr 29 '14 at 17:56

score 0 · Answer 2 · edited May 23 '17 at 12:24

Some databases are not set case sensitive on text comparison as a default, like MySQL for example.

where("username in (?)", usernames.map(&:downcase))

The above delivers the desired result on MySQL unless default has been overwritten. See this post on Stackoverflow

I do agree however that adding the lower function should be the preferred method unless you like to gain a small speed advantage since the database server doesn't have to convert the string first.

> Contact.first
Contact Load (172.3ms)  SELECT `contacts`.* FROM `contacts` ORDER BY `contacts`.`id` ASC LIMIT 1
=> #<Contact id: 1, name: "John"> 

> Contact.where(name: "john")
Contact Load (171.9ms)  SELECT `contacts`.* FROM `contacts` WHERE `contacts`.`name` = 'john'
=> #<ActiveRecord::Relation [#<Contact id: 1, name: "John"]> 

> Contact.where(name: "JOHN")
Contact Load (172.3ms)  SELECT `contacts`.* FROM `contacts` WHERE `contacts`.`name` = 'JOHN'
=> #<ActiveRecord::Relation [#<Contact id: 1, name: "John"]>

Kirti's answer is more appropriate because it keeps in mind the index I have on `lower(username)`, but generally, yes. — Josh Smith, Apr 29 '14 at 17:56
Thanks Daniel - will keep in mind. Was meant to be an answer - removing the 'question style' line. — smile2day, May 01 '14 at 08:32

Replacing where(username: usernames) with case-insensitive finder

2 Answers2