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 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

Why is `Artist.find_or_create_by_name(artist_name, :case_sensitive => false)` harder to implement? — Harish Shetty, Mar 12 '10 at 21:05
@KandadaBoggu because `find_or_create_by_name` is created dynamically by `method_missing`? Maybe it's not harder -- how would you implement it? — Tom Lehman, Mar 12 '10 at 22:14
Implement it like any other class method. If you have defined this method, then `method_missing?` is never invoked. — Harish Shetty, Mar 12 '10 at 22:44
But would do I access the default `find_or_create_by_name` behavior when the user *doesn't* do `:case_sensitive => false`? (I guess duplicating it wouldn't be a big deal) — Tom Lehman, Mar 12 '10 at 22:58

score 18 · Answer 1 · answered Jan 07 '15 at 09:27

18

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

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

answered Jan 07 '15 at 09:27

Erica Tripp

316
2
8

Sounds like this method still exists in rails 4.2 but is deprecated. – Jared Oct 11 '16 at 23:33
2

It looks like it's still working in rails 5.0 without any deprecation warnings. Where did you see that the method was deprecated? – Erica Tripp Oct 13 '16 at 01:53
My bad, you're correct. I just thought it was deprecated since I couldn't find it in the documentation. Turns out it's just undocumented. – Jared Oct 13 '16 at 18:11
Working fine on rails 5.1 too. – Sandip Subedi May 21 '19 at 21:16

score 7 · Accepted Answer · answered Mar 13 '10 at 17:37

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)

score 5 · Answer 3 · edited May 23 '17 at 12:09

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

score 1 · Answer 4 · edited May 23 '17 at 12:09

1

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

edited May 23 '17 at 12:09

Community

1
1

answered Mar 12 '10 at 21:02

alex.zherdev

23,914
8
62
56

Case-insensitive find_or_create_by_whatever

4 Answers4

Linked