4

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!

Matt Jones
  • 59
  • 1
  • 3

3 Answers3

8

You missed LIKE for city.

Model.where('city LIKE ? AND name LIKE ?', "%#{q}%", "%#{b}%");
xdazz
  • 158,678
  • 38
  • 247
  • 274
  • or maybe should using arel to say LIKE, http://stackoverflow.com/questions/4430578/how-to-do-a-like-query-in-arel-and-rails-3 – Eric Guo Nov 02 '14 at 01:47
6

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]}%"})
kakubei
  • 5,321
  • 4
  • 44
  • 66
3

Try this:

Model.find(:all, :conditions => ["city = ? AND name like ?", q, b])
denis.peplin
  • 9,585
  • 3
  • 48
  • 55
  • 1
    Consider pointing out the difference/issue, especially when it's subtly. –  Aug 21 '12 at 04:17