Creating dynamic WHERE clauses with ActiveRecord
Posted by Matt Osentoski Wed, 24 Jan 2007 19:58:00 GMT
Hibernate has a class called Criteria that's used to help eliminate the logic in dynamic queries. I was building a Ruby on Rails application recently and wanted similar functionality, but didn't see the equivalent of a Criteria object in ActiveRecord. I searched around for a 3rd party library and found a few, but wasn't interested in a heavy API. So, I rolled my own Class to create dynamic WHERE clauses.The class is called SqlBuilder and works on the principle that the ActiveRecord find() method can accept a String and Hash of parameters in the :conditions clause. What my class does is create the SQL string, inserting symbols in place of values.
To use my class in your own code, grab the sql_builder.rb file here and follow the sample code below:
require 'sql_builder'
builder = SqlBuilder.new
builder.where_helper('division', params['division'], 'divisions.id')
builder.where_helper('vehicle', params['vehicles'], 'vehicles.id')
builder.where_helper('notes', params['notes'].strip, 'claims.notes', 'AND', 'LIKE')
sql_conditions, sql_conditions_hash = builder.get_sql_conditions()
@claims = Claim.find(:all, :include => [:divisions, :vehicles],
:conditions => [sql_conditions, sql_conditions_hash]) Disclaimer: This is a very, very simple Class and probably won't work for every scenerio. If you have very complex needs you should check out some of the other libraries, that are out there.