Rails Newbie: Appending conditions in activerecord?

I have a form which is used to filter queries. Some of the form attributes are optional; I'm just wondering how to append them as activerecord conditions if (and only if) they have a set value?

There's a fair few of them, so I'd rather not make a separate query for each pattern of potential values. Any suggestions?

To give a specific example:

people = People.paginate(
  :all,
  :include => [:people_postcodes, :people_specialties, :people_states],
  :conditions => ["people_specialties.people_type_id = %s AND (people_postcodes.postcode_id = %s OR people_states.state_id = %s)" % [self.people_type_id, postcodeid.id, stateid]], 
  :page => page, 
  :per_page => 16
)

How would I best go about creating an extra condition (say 'nationality') only if the optional 'nationality' attribute is populated?

Answers


First off, your conditions are a little insecure. You're doing basic ruby text substitution, which will let site users inject whatever malicious sql they want. Instead, format it like this:

people = People.paginate(
  :all,
  :include => [:people_postcodes, :people_specialties, :people_states],
  :conditions => ["people_specialties.people_type_id = ? AND (people_postcodes.postcode_id = ? OR people_states.state_id = ?)", self.people_type_id, postcodeid.id, stateid], 
  :page => page, 
  :per_page => 16
)

To answer your question, there's no natural way to tack on another condition in Rails 2.x. I would do this:

conditions = ["people_specialties.people_type_id = ? AND (people_postcodes.postcode_id = ? OR people_states.state_id = ?)", self.people_type_id, postcodeid.id, stateid]

if params[:nationality]
  conditions.first += " and nationality = ?"
  conditions.push params[:nationality]
end

people = People.paginate(
  :all,
  :include => [:people_postcodes, :people_specialties, :people_states],
  :conditions => conditions, 
  :page => page, 
  :per_page => 16
)

In the example above, I'm assuming nationality is passed in as a parameter, but adjust as needed. I create the original conditions array, then append the first element (the actual condition string) and add one more element to the end of the array: the nationality value.

I hope this helps!


Need Your Help

Extending QGraphicsScene

qt qgraphicsitem qgraphicsscene

I am extending QGraphicsItem to be added to a extended QGraphicsSene. when I added the extended item to the scene and the scene to the graphics view in the normal way it shows the image but when I ...

Mapbox Driving Directions and jQuery

jquery mapbox driving-directions

I am working with the Mapbox driving directions plugin, doing my own geocoding from within a rails app and using jQuery to set the value of the #mapbox-directions-origin-input and

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.