Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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), but this seems much harder to implement

like image 523
Tom Lehman Avatar asked Mar 12 '10 20:03

Tom Lehman


3 Answers

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

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

like image 180
Erica Tripp Avatar answered Sep 28 '22 11:09

Erica Tripp


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

like image 20
Harish Shetty Avatar answered Sep 28 '22 10:09

Harish Shetty


Talked about this one here. No one was able to come up with a solution better than yours :)

like image 32
alex.zherdev Avatar answered Sep 28 '22 11:09

alex.zherdev