Rewrite a SQL query to work on heroku

Question

Hi,

How would i rewrite this rails query to run on Heroku (Portegres)

@students = Student.find(:all, 
                  :conditions =>["(concat(first_name, ' ', last_name) LIKE ?) OR
                                  (concat(first_name, ' ', middle_names, ' ', last_name) LIKE ?)",
                                 "%#{params[:search]}%", "%#{params[:search]}%"]
                        )

Answer 1

Ran in to this problem too. Postgres is case-sensitive with the LIKE operator, so here's what I did in a project. Also, I don't like to run postgres locally, so I have it run with LIKE in development (mysql/sqlite) and ILIKE in production (postgres). ILIKE is "case insensitive like"

/config/environment.rb

  DATABASE_OPERATOR = {
    :like_operator => "LIKE"
  }

/config/environments/production.rb

  DATABASE_OPERATOR = {
    :like_operator => 'ILIKE' #postgres awfulness
  }

Query

@students = Student.find(:all, :conditions => ['(concat(first_name, " ", last_name) #{DATABASE_OPERATOR[:like_operator]} ?) OR (concat(first_name, " ", middle_names, " ", last_name) #{DATABASE_OPERATOR[:like_operator]} ?)', "%#{params[:search]}%", "%#{params[:search]}%"])

Answer 2

Use pipes || instead of CONCAT, that's standard SQL:

@students = Student.find(:all, :conditions => ['(first_name || last_name LIKE ?) OR (first_name || middle_names || last_name LIKE ?)', "%#{params[:search]}%", "%#{params[:search]}%"])

Check your quotes as well, you need single quotes ' for strings, double quotes " for database objects. MySQL accepts both, depending on the configuration, other databases only accept the standard.

Answer 3

Use following

Student.find(:all, 
                  :conditions =>["(concat(first_name, ' ', last_name) LIKE :text) OR
                                  (concat(first_name, ' ', middle_names, ' ', last_name) LIKE :text)",
                                 {:text=>"%#{params[:search]}%"]
                        )

Answer 4

There's no point in using concat(). @Frank's answer is close, but the logic is broken.

where (first_name || last_name LIKE ?) evaluates to

"where first_name evaluates to true or where last_name contains search term"

This should get you the expected result

Student.all(
  :conditions => ["
    first_name like :search ||
    last_name like :search  ||
    middle_names like :search
    ",
    {:search => "%#{params[:search]}%"}
  ]
)

This will match all rows on students table that have params[:search] in any part of their name.