Case-insensitive find_or_create_by_whatever
I want to be able to do Artist.case_insensitive_find_or_create_by_name(artist_name)
[1] (and have it work on both sqlite and postgreSQL)
What's the best way to accomplish this? Right now I'm just adding a method directly to the Artist
class (kind of ugly, especially if I want this functionality 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)
开发者_如何学Python, but this seems much harder to implement
Rails 4 gives you a way to accomplish the same thing:
Artist.where('lower(name) = ?', name.downcase).first_or_create(:name=>name)
This answer is for the additional questions asked in the question comments.
You wont be able to call the default find_or_create_by_name
if you override that 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)
You have to create an index based on the database.
postgreSQL
Create a lower case index on artist_name
column.
CREATE INDEX lower_artists_name ON artists(lower(artist_name))
mySQL
Searches are case insensitive
sqlLite
Create a index on artist_name
column with collate parameter
CREATE INDEX lower_artists_name ON artists( artist_name collate nocase)
Now you can use find_or_create in a DB independent manner:
find_or_create_by_artist_name(lower(artist_name))
Reference
PostgreSQL: Case insensitive search
sqlLite: Case insensitive search
Talked about this one here. No one was able to come up with a solution better than yours :)
精彩评论