Pulling unique case-insensitive values

Question

I'm using jQuery autocomplete in a few areas. It makes a call to this method:

def index
    @schools = School.order(:name).where("name like ?", "%#{params[:term]}%").limit(10)
    render json: @schools.map(&:name)
end

Initially I didn't handle case sensitivity well, so I have some duplicate names in differing cases (ie. "Rutgers" and "rutgers".

I'd like to change the above method to return only unique values, ignoring the case, so only one "Rutgers" result will be returned.

I'm going to clean up the DB but a quick temp fix for this would be great!

Case insensitive. If there are two entries: "Rutgers" and "rUtGeRs", and a user searches for "RUTG", I just want to return a single result. I'm using SQLite3 in dev, postgres in production. — opticon, Jun 11 '14 at 16:14
Also, note, `LIKE` queries are highly inefficient and probably not sufficient for the responsiveness you expect on an autocomplete field. — Damien Roche, Jun 11 '14 at 16:29
"I'm using SQLite3 in dev, postgres in production." Please stop doing that to yourself. Use the same database in dev and production, ORMs do **not** provide database portability. — mu is too short, Jun 11 '14 at 16:39

Uri Agassi · Answer 1 · 2014-06-11T16:19:24.797

1

I guess it depends on which DB you are using, but you can try doing something like this:

def index
  @school_names = School.order("lower(name)")
                        .where("name ilike ?", "%#{params[:term]}%")
                        .distinct.limit(10).pluck("lower(name)")
  render json: @school_names.map(&:downcase)
end

The above will work with POSTGRESQL, for Sqlite, this will work:

def index
  @school_names = School.order("lower(name)")
                        .where("lower(name) LIKE lower(?)", "%#{params[:term]}%")
                        .distinct.limit(10).pluck("lower(name)")
  render json: @school_names.map(&:downcase)
end

edited Jun 11 '14 at 16:19

answered Jun 11 '14 at 16:14

Uri Agassi

36,848
14
76
93

The 'pluck' method returns an array, which the rest of the methods don't work on :P – opticon Jun 11 '14 at 16:20
1

Look above, I made the edit to my comment 10 minutes ago. And why can't I tell someone that their solution didn't work for me? – opticon Jun 11 '14 at 16:25
@opticon Because, your original question still lacks the information need to help you(If I don't care the comment). You have updated the comment after my second repetition. This answer came out from *guess*, that *Uri* mentioned. Many of us don't answer on guess, but some do although. While you don't ask question properly, don't expect others also to come correctly... That's my whole point. – Arup Rakshit Jun 11 '14 at 16:59
@opticon - I've updated my answer(moved `pluck` to the end of the query), also see this question: http://stackoverflow.com/questions/11249059/generic-ruby-solution-for-sqlite3-like-or-postgresql-ilike – Uri Agassi Jun 11 '14 at 17:08

Pulling unique case-insensitive values

1 Answers1