Case insensitive find_or_create_by_whatever

Question

I want to be able to do Artist.case_insensitive_find_or_create_by_name(artist_name) [1] (and it works with both sqlite and postgreSQL)

What is the best way to achieve this? Right now I'm just adding a method directly to the Artist class (kind of ugly, especially if I want this function in another class, but whatever):

  def self.case_insensitive_find_or_create_by_name(name) first(:conditions => ['UPPER(name) = UPPER(?)', name]) || create(:name => name) end 

[1]: Well, ideally it would be Artist.find_or_create_by_name(artist_name, :case_sensitive => false) , but it's a lot harder to implement

Answer 1

This answer is responsible for additional questions asked in the comments to the question.

You cannot call find_or_create_by_name by default if you override this method. But you can implement your own, as shown below:

 def self.find_or_create_by_name(*args) options = args.extract_options! options[:name] = args[0] if args[0].is_a?(String) case_sensitive = options.delete(:case_sensitive) conditions = case_sensitive ? ['name = ?', options[:name]] : ['UPPER(name) = ?', options[:name].upcase] first(:conditions => conditions) || create(options) end 

Now you can call the overridden method as follows:

 User.find_or_create_by_name("jack") User.find_or_create_by_name("jack", :case_sensitive => true) User.find_or_create_by_name("jack", :city=> "XXX", :zip => "1234") User.find_or_create_by_name("jack", :zip => "1234", :case_sensitive => true) 

Answer 2

Rails 4 gives you a way to accomplish the same thing:

Artist.where('lower(name) = ?', name.downcase).first_or_create(:name=>name)

Answer 3

You need to create an index based on the database.

PostgreSQL

Create a lowercase index in the artist_name column.

 CREATE INDEX lower_artists_name ON artists(lower(artist_name)) 

mySQL

Claims are case insensitive

sqlLite

Create index in artist_name column with collation

 CREATE INDEX lower_artists_name ON artists( artist_name collate nocase) 

Now you can use find_or_create in a DB-independent way:

 find_or_create_by_artist_name(lower(artist_name)) 

Link

PostgreSQL: case insensitive search

sqlLite: case insensitive search

Answer 4

Talk about it here . No one could find a solution better than yours :)