CODEWITHSUNDEEP
ruby-on-railssqlitepostgresqlheroku
innonate
innonate

Reputation: 159

Multi-column Simple-Search from SQLite to Heroku/PostgreSQL

def self.search(search)
  if search
    where('name OR username OR bio LIKE ?', "%#{search}%")
  else
    scoped
  end
end

The above code works fine on my development server using SQLite, but since Heroku uses PostgreSQL it breaks (seems you can only use "OR" in truly boolean queries).

Is there a better way to implement this simple search so it works with PostgreSQL or do I have to move to a more complex search solution?

Upvotes: 0

Views: 417

Answers (1)

mu is too short
mu is too short

Reputation: 434665

Are you looking for something like this?

def self.search(search)
  if search
    where('name IS NOT NULL OR username IS NOT NULL OR bio LIKE ?', "%#{search}%")
  else
    scoped
  end
end

If name and username can be NULL or empty then you'll want to use COALESCE:

def self.search(search)
  if search
    where("COALESCE(name, '') <> '' OR COALESCE(username, '') <> '' OR bio LIKE ?", "%#{search}%")
  else
    scoped
  end
end

These should work the same in SQLite and PostgreSQL.

Upvotes: 1

Related Questions