1

I am trying pull information from a contacts table based on multiple like conditions. So far I have come up with the following

conditions = ""
conditions << "email_address LIKE '%#{params[:email_address]}%'" unless params[:email_address].blank?
conditions << " AND first_name LIKE '%#{params[:first_name]}%'" unless params[:first_name].blank?
conditions << " AND last_name LIKE '%#{params[:last_name]}%'" unless params[:last_name].blank?
conditions.sub!(/^AND/, '')


if !conditions.blank?
  @contacts = Contact.where(conditions).page(params[:page]).per(10)
else
  @contacts = Contact.all.page(params[:page]).per(10)
end

What I was wondering is ... is this the best way to do this? I would have thought there would be a nice way to add multiple conditions in the form of a hash and somehow specify that I want to use OR/AND and like.

I am fairly new to rails and google is not really helping much.

Thanks.

Ryan-Neal Mes
  • 6,003
  • 7
  • 52
  • 77

1 Answers1

1

Just append the where calls directly to a scope:

@contacts = Contact.scoped

@contacts = @contacts.where("email_address LIKE '%?%'", params[:email_address]) if params[:email_address].present?
@contacts = @contacts.where("first_name LIKE '%?%'", params[:first_Name]) if params[:first_name].present?
@contacts = @contacts.where("last_name LIKE '%?%'", params[:last_name]) if params[:last_name].present?

You can use a simple loop to make it less repetative:

%(email_address first_name last_name).each do |field|
  @contacts = @contacts.where("#{field} like '%?%'", params[field]) if params[field].present?
end

And do not build queries by hand by directly substituting user input into your query string. Rails makes that hard to do on purpose: You're bypassing all of Rails' sanitization and opening yourself to SQL injection.

I would have thought there would be a nice way to add multiple conditions in the form of a hash and somehow specify that I want to use OR/AND and like.

There is, but it only works with AND and =:

@contacts.where(first_name: "bob", last_name: "smith")
# select ... where first_name = 'bob' and last_name = 'smith'
user229044
  • 232,980
  • 40
  • 330
  • 338
  • Thanks for the detailed answer. It looks like exactly what I need. However I am getting an error. undefined method `scoped'. Can you scope views (as in database view)? The contacts table is actually a view into another database. – Ryan-Neal Mes Jan 28 '14 at 18:47
  • [`scoped`](http://apidock.com/rails/ActiveRecord/NamedScope/ClassMethods/scoped) is a simple method on ActiveRecord models and associations that has existed since Rails 3, if `Contact` inherits from ActiveRecord::Base it should respond to `scoped`. Failing that, you can simply use `@contacts = Contact` to initialize instead – user229044 Jan 28 '14 at 18:50
  • I am pretty new to rails so didn't know about it till now :) It doesn't seem to be working. I am thinking because Contact is a view in the database. Thoughts? I have implemented it the same way you have written up. – Ryan-Neal Mes Jan 28 '14 at 18:51
  • After further investigation is seems scoped is deprecated in 4.1 which I am using ... trying to find alternative. – Ryan-Neal Mes Jan 28 '14 at 19:21
  • 1
    Looks like you can use `.all` or `.where(nil)` now: http://stackoverflow.com/questions/18198963/with-rails-4-model-scoped-is-deprecated-but-model-all-cant-replace-it – user229044 Jan 28 '14 at 19:23
  • Yeah found that. Just got it working. Had to tweek your post a little. Thought you might want to check it out. Thanks again. http://stackoverflow.com/questions/19105706/rails-4-like-query-activerecord-adds-quotes-problems – Ryan-Neal Mes Jan 28 '14 at 19:28