Does ActiveRecord support dynamic conditions in sql statements?

Question

I want to make a SQL query in a Rails app that adds a condition when a drop-down menu is added. Obviously I don't want to search with a blank condition if the drop down is not selected. How can I develop a dynamic condition in an SQL statement?

untested example:

When dropdown is not selected: Object.find("billy," :conditions => {})

When dropdown is selected: Object.find("billy," :conditions => {"last_name => "johnson"})

Thank you for your input!

Answer 1

You could build a conditions hash and just pass it in...

Answer 2

Maybe this?

Object.find("billy", :conditions => last_name.nil? ? {} : {:last_name => last_name})

Answer 3

Not quite sure what you are doing, but the general way to achieve this is to have the drop-down's value (not visible text) be the ID of the object you wish to select. If you don't have a selection then you can use something like this:

last_name = unless params[:object][:last_name]

conditions = []
conditions << "last_name = ?" unless last_name.blank?
conditions << last_name unless last_name.blank?

Object.find("billy", :conditions => conditions)

Check out this link to learn more about ActiveRecord::Base.find() methods and the proper way to use them. Also look at this great guide to learn how to protect yourself from SQL injection attacks.

Edit 1: Modified code to be cleaner. This method also has the advantage of being able to easily add separate conditionals, just make sure that you add all of the "something = ?" before you add the associated value (param).

Edit 2: In case you haven't seen the select form helper, you might consider using it for your drop down. Makes life a lot easier :-)

Answer 4

Assuming your #find is in a controller and that the value selected is coming in as an item in params (and I think you should consider changing things if not!) then you should be able to do something like this:

if params[:last_name] # or whatever it's actually called
  Object.find("billy," :conditions => {'last_name = ?', params[:last_name]})
else
  Object.find("billy")
end

If you're using a reasonably recent version of ActiveRecord (2.1 should do it, possibly 2.0) then you might shift that logic down to a named_scope:

class Object < ActiveRecord::Base
  named_scope :for_last_name, lambda { |nm| { :conditions => nm.nil? ? {} : { last_name => nm } }

and then your controller boils down to (and we love thin controllers)

Object.for_last_name(params[:last_name])

(All somewhat untested)

Answer 5

I'm using SmartTuple for that. It's simple, yet offers a systematic approach to building "conditional conditions".