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

Question

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?

Are you trying to find an object that has (name LIKE search) or (username LIKE search) or (bio LIKE search)? — Seth Archer Brown
– Seth Archer Brown, Commented Dec 20, 2010 at 20:19

mu is too short · Accepted Answer · 2010-12-04 17:32:24Z

1

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.

edited Dec 4, 2010 at 17:32

answered Dec 4, 2010 at 5:48

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

2 Comments

user330315 Over a year ago

I don't know which programming language that is and how the SQL literal gets translated, but using double quotes for character literals is not a valid syntax in PostgreSQL (and most other DBMS)

mu is too short Over a year ago

Right, thanks @a_horse_with_no_name, I had my single and double quotes backwards. I get my languages confused sometimes; I guess that'll teach me not to check my work. I reversed the quotes so Ruby, PostgreSQL, and SQLite should all be happy now.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related