Converting SQL command to Active Record for LIKE Command

Question

I have a sql query which I have written in the following way:

SELECT "books".* FROM "books" WHERE (title LIKE '%intro%' OR isbn LIKE '%intro%' or status like '%intro%' or author like '%intro%' or description like '%intro%')

In my rails model I wrote the following way:

def self.search(query)
  where(['title LIKE ? OR isbn LIKE ? or status like ? or author like ? or description like ?', "%#{query}%", "%#{query}%","%#{query}%","%#{query}%","%#{query}%"])
end

This works on my sqlite3 database and returns all rows with the query "intro".

However in my postgres database it returns only one row. Could some one suggest me a way.

Contents of my table:

id  isbn    title   description author  status
2   15  Introduction to english Introduction to english veena kapoor    available
3   16  Introduction to hindi   Introduction to hindi   rastogi vinayak available
4   17  Introduction to sanskrit    Introduction to sanskrit    edward solomon  available

Does the original SQL query return the result you expect in the Postgres database? When you look at the Rails log, what SQL is your Rails method generating? — Jordan Running, Oct 08 '15 at 04:09
Can you edit your question to include the data from the Postgres database that you expect to be returned but isn't? — Jordan Running, Oct 08 '15 at 04:12
@huanson Could you suggest a way to make this work in both sqlite & postgres ? — user1692342, Oct 08 '15 at 04:14
Not the column names. The actual data in the database rows. We can't help you figure out why a query didn't select the rows if we don't know what's in the rows. — Jordan Running, Oct 08 '15 at 04:16
PS: You could make that a lot nicer with named placeholders: `where('title ilike :pat or isbn ilike :pat ...', :pat => "%#{query}%")`. — mu is too short, Oct 08 '15 at 04:45

score 2 · Accepted Answer · edited May 23 '17 at 11:52

The condition WHERE title LIKE '%intro%' (for example) won't match the value 'Introduction to english' because LIKE is case-sensitive in Postgres. You must ILIKE to do a case-insensitive match.

This may be a problem, since ILIKE doesn't work in SQLite. Take a look at this question for some potential solutions: Generic Ruby solution for SQLite3 "LIKE" or PostgreSQL "ILIKE"? The best solution (as proffered by the top-rated answer there) is to use the same database (Postgres) in both development and production.

Converting SQL command to Active Record for LIKE Command

1 Answers1