MySQL syntax in Rails 3, NOT Case sensitive searching, not working on Heroku?

Question

I've been following the Railscast for adding searching, sorting, and pagination to my app. I've modified my search logic in the model to search to columns (description and title). This seems to be working. I've also changed it to search non case sensitive. This seems to work perfect in my local app, but when I push it to heroku, I can only search by lowercase. Search with any capital letters at all produces no results, even if the case matches the results.

here is the code in my model:

  def self.search(search)
    if search
      where('LOWER (description) LIKE ? OR LOWER (title) LIKE ?', "%#{search}%" , "%#{search}%")
    else
      scoped
    end
  end

I'm not sure that this is your issue, but heroku uses Postgres, so any MySQL-specific syntax will not work (I don't know that any of that is mysql-specific, however). — cam
– cam, Commented Jan 12, 2011 at 19:07
That shouldn't be the issue: Postgres also has a LOWER function. — David Sulc
– David Sulc, Commented Jan 12, 2011 at 20:05

David Sulc · Accepted Answer · 2011-01-12 20:07:29Z

5

Try

where("LOWER (description) LIKE ? OR LOWER (title) LIKE ?", "%#{search.downcase}%" , "%#{search.downcase}%")

edited Jan 12, 2011 at 20:07

answered Jan 12, 2011 at 19:04

David Sulc

26k3 gold badges55 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Elliot Over a year ago

Thanks David, but this unfortunately returned a MySQL syntax error

David Sulc Over a year ago

I edited my reply (I had misread your question). Does this query work?

David Sulc Over a year ago

FYI the issue was that you were comparing a string to (forced) lowercase database values. If your string had an uppercase character, the comparison would fail. With this query, you also force the search function argument to be lower case, therefore ensuring a match (since both strings are guaranteed to be lowercase).

d135-1r43 · Accepted Answer · 2011-01-12 21:53:39Z

0

I had this problem, too. Heroku uses PostgreSQL and the LIKE-Operator there is strongly typed to strings. Check this http://www.postgresql.org/docs/8.3/interactive/functions-matching.html

answered Jan 12, 2011 at 21:53

d135-1r43

2,4851 gold badge25 silver badges33 bronze badges

Collectives™ on Stack Overflow

MySQL syntax in Rails 3, NOT Case sensitive searching, not working on Heroku?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related