Rails activerecord LIKE AND clause error

Question

Here is an activerecord query i'm trying to use in rails

q = "Manchester"
b = "John Smith"

Model.find(:all, :conditions => ["city ? AND name like ?", q, b])

but i get this error in rails console

ActiveRecord::StatementInvalid: SQLite3::SQLException: near "'Manchester'": syntax error: SELECT "model".* FROM "model" WHERE (city 'Manchester' AND name like 'John Smith')

Please help!

Answer 1

You missed LIKE for city.

Model.where('city LIKE ? AND name LIKE ?', "%#{q}%", "%#{b}%");

Answer 2

You can also use this syntax which is a lot more readable than trying to figure out which ? goes with which variable. I mean if you have 1 or 2 it's fine, but once you have more it gets pretty ugly.

Model.where("city LIKE :city AND name LIKE :name", { city: "%#{q}%", name: "%#{b}%"  })

The placeholders and hash key can be anything you like as long as they match (don't use :city and then hamster: in the hash key for example).

The nice thing about this is that you can also use one variable for multiple searches:

where("user LIKE :term OR email LIKE :term OR friends LIKE :term", { term: "%#{params[:term]}%"})

Answer 3

Try this:

Model.find(:all, :conditions => ["city = ? AND name like ?", q, b])