0

I have some basic search implemented and I am trying to match multiple attribute to the 1 set of keywords.

I have this:

listings = Listing.order(:headline)
listings = listings.where("headline like ?", "%#{keywords}%") if keywords.present?

I have multiple attributes for listings that I would like to check to see if the keywords appear in - e.g. say listing.neighborhood.name or listing.type.name.

How do I write all of that in 1 statement? I tried:

listings = listings.where("headline like ? or neighborhood.name like ?", "%#{keywords}%")

But that returned this error:

ActiveRecord::PreparedStatementInvalid: wrong number of bind variables (1 for 2) in: headline like ? or neighborhood.name like ?

One obvious solution is just to add "%#{keywords}%" again...but that hardly seems DRY.

How do I add check both the neighborhood.name and type.name and others in that 1 query? Or should I be approaching this another way?

Thanks.

Edit 1:

When I try the non-DRY way of simply adding another {keywords} parameter, this is the error I get:

ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: neighborhood.name: SELECT "listings".* FROM "listings"  WHERE (headline like '%sterling place%') AND (headline like '%sterling place%' or neighborhood.name like '%sterling place%') ORDER BY headline.

Which I think is because neighborhood is a foreign_key on my listings model, and not an attribute in and of itself. There is a simple association between the listings & neighborhood model.

marcamillion
  • 32,933
  • 55
  • 189
  • 380

3 Answers3

2

Try this (still a non-DRY way) :

key = "%#{keywords}%"
listings = Listing.includes(:neighborhood).where("headline like ? or neighborhoods.name like ?", key, key)
siekfried
  • 2,964
  • 1
  • 21
  • 36
  • Perfect. This is exactly what I was looking for. Thanks meng! Although, it would be nice if this could be DRY-ed up, but you got the essence of what I am trying to achieve. – marcamillion Feb 11 '13 at 13:41
  • Quick question, why do I get this error on Heroku, but not in development: `ActiveRecord::ConfigurationError: Association named 'neighborhoods' was not found; perhaps you misspelled it?` This works locally, but when I push to Heroku that's the error I am getting in the console. Thoughts? – marcamillion Feb 11 '13 at 14:18
  • I broke out this error to a new question - http://stackoverflow.com/questions/14814126/heroku-join-includes-not-working-like-sqlite-dev-environment – marcamillion Feb 11 '13 at 14:35
  • OK fine, hope you'll find a solution soon and glad I could help you until now! – siekfried Feb 11 '13 at 14:37
2

I don't think you have many option to make it DRY... but you could do something like: 

listings = listings.where("headline like :keywords or neighborhood.name like :keywords", :keywords => "%#{keywords}%")

Edit: I didn't realise the association... siekfried is right... just a little more DRY if you start adding more columns:

listings = Listing.includes(:neighborhood).where("listings.headline like :keywords or neighborhoods.name like :keywords", :keywords => "%#{keywords}%")
Community
  • 1
  • 1
gabrielhilal
  • 10,660
  • 6
  • 54
  • 81
  • The issue I seem to be bumping up against is the association (i.e. matching the keywords to the `neighborhood.name`). Is it possible to match a keyword to an association like that? Or do I have to do it in a different where clause? When I tested your suggestion, I got the error I put in my question under `Edit 1`. – marcamillion Feb 11 '13 at 13:34
  • oh...sorry, I didn't realise the association... you will need `includes` or `joins` as per siekfried's answer. – gabrielhilal Feb 11 '13 at 13:46
  • Btw, I like this solution, but now I am getting this error on Heroku - `ActiveRecord::StatementInvalid: PG::Error: ERROR: missing FROM-clause entry for table "neighborhood"`. Thoughts? – marcamillion Feb 11 '13 at 14:20
  • change :neighborhood to :neighborhoods... it must be the table name. – gabrielhilal Feb 11 '13 at 14:28
  • Now I am getting this error: `ActiveRecord::ConfigurationError: Association named 'neighborhoods' was not found; perhaps you misspelled it?`. :| – marcamillion Feb 11 '13 at 14:31
  • Btw, I broke this out to a new question - http://stackoverflow.com/questions/14814126/heroku-join-includes-not-working-like-sqlite-dev-environment – marcamillion Feb 11 '13 at 14:34
  • I posted a new answer in the new post... when it goes to SQL I think the table name must be specified. – gabrielhilal Feb 11 '13 at 14:55
1

Try:

listings = listings.where("headline like ? or neighborhood.name like ?", "%#{keywords}%", "%#{keywords}%")
sjain
  • 23,126
  • 28
  • 107
  • 185