CODEWITHSUNDEEP
ruby-on-railsactiverecord
Matt Jones
Matt Jones

Reputation: 59

Rails activerecord LIKE AND clause error

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!

Upvotes: 4

Views: 7693

Answers (3)

kakubei
kakubei

Reputation: 5400

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]}%"})

Upvotes: 6

xdazz
xdazz

Reputation: 160973

You missed LIKE for city.

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

Upvotes: 8

denis.peplin
denis.peplin

Reputation: 9881

Try this:

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

Upvotes: 3

Related Questions