Object-oriented approach to ActiveRecord#find

ActiveRecord is great! Only it would be nice if there would be an Object Oriented way to build the options given to the ActiveRecord#find method. Think HQL queries versus Criteria objects in Hibernate. If you’re asking yourself why someone would go and complicate a beautiful thing like the syntax to the find options, consider the following example. It’s a standard scenario where one is listing products based on a few search criteria entered by the user.

def list_products
	conditions = []

	if params[:filter_category] != ""
		conditions << "category_id = " + params[:filter_category]
	end

	if params[:only_in_stock] == "1"
		conditions << "stock > 0"
	end

	conditions_sql = conditions.join(' AND ')

	@products = Product.find(:conditions => conditions_sql)
end

This doesn’t look too bad, but there is a problem. The user can fiddle with the request and put SQL injection code in the filter_category parameter. This is why ActiveRecord provides parameter binding. Rewriting our code to support parameters would look something like this:

def list_products
	conditions_sql = []
	conditions_parameters = []

	if params[:filter_category] != ""
		conditions << "category_id = ?"
		conditions_parameters << params[:filter_category]
	end

	if params[:only_in_stock] == "1"
		conditions << "stock > 0"
	end

	conditions = conditions.join(' AND ') + conditions_parameters

	@products = Product.find(:conditions => conditions_parameters)
end

This looks a bit worse, especially when we have a whole bunch of parameters. Introducing the RecordFinder class, a nicer way to write this would be:

def list_products
	finder = RecordFinder.new

	if params[:filter_category] != ""
		finder.add "category_id = ?", params[:filter_category]
	end

	if params[:only_in_stock] == "1"
		finder.add "stock > 0"
	end

	@products = Product.find(:conditions => finder.to_conditions)
end

This can also work with multiple parameters:

finder = RecordFinder.new
finder.add "email = ? AND password = ?", params[:email], params[:password]
@user = User.find(:first, finder.to_conditions)

We can add shorthand methods for common conditions:

finder.add_wildcard "product.name", params[:name_wildcard]
finder.add_ref :category_id, params[:category_id]

We can change the combination of conditions from AND to OR:

finder = RecordFinder.new('OR')
finder.add "product.new = 1"
finder.add "product.hot = 1"
@hot_or_new_product = Product.find(:all, finder.to_conditions)

We can use sub-finders, for instance this SQL:

WHERE
	product.stock > 0
	AND (
		product.new = 1
		OR
		product.hot = 1
	)

Could be written as:

finder = RecordFinder.new
finder.add "product.stock > 0"

sub_finder = RecordFinder.new('OR')
sub_finder.add "product.new = 1"
sub_finder.add "product.hot = 1"

finder.add_finder sub_finder

And perhaps most importantly, we can extend the RecordFinder to put shared business logic filters in named methods, in stead of repeating the conditions in different controllers in the application:

class ProductFinder  < RecordFinder
	def in_stock
		add "product.stock > 0"
	end

	def new_or_hot
		sub_finder = RecordFinder.new('OR')
		sub_finder.add "product.new = 1"
		sub_finder.add "product.hot = 1"
	end

	... other business filters ...
end

Our ProductController can use it like this:

def list_products
	finder = ProductFinder.new
	finder.in_stock
	finder.new_or_hot

	@products = Product.find(:all, :conditions => finder.to_conditions)
end

Suppose we must change the business logic not to show products for which all the stock is reserved:

class ProductFinder < RecordFinder
	def in_stock
		add "product.stock > product.reserved_stock"
	end

	....
end

We change it once in the in_stock method, and all controllers using this method get the correct filter.The code to the RecordFinder class is below. Any ideas on how to improve it, make the syntax more concise or add useful features is of course welcome.

class RecordFinder

  attr_reader :parameters
  attr :order_by, true

  def initialize (bool_mode = 'AND')
    @bool_mode = bool_mode
    @sqls = []
    @parameters = []
    @includes = []
    @order_by = ""
  end

  def add (sql, *params)
    @sqls << sql
    @parameters += params
  end

  def add_ref(field, int)
    add "#{field.to_s} = ?", int
  end

  def add_wildcard(field, value)
    add "#{field.to_s} LIKE ?", "%#{value}%"
  end

  def add_finder(finder)
    @sqls << finder.sql_string
    @parameters += finder.parameters
  end

  def sql_string
    @sqls.collect{|sql| "(#{sql})"}.join(" #{@bool_mode} ")
  end

  def to_conditions
    if @sqls.length > 0
      [ sql_string ] + @parameters
    else
      nil
    end
  end

  def to_find_options
    {
      :include => @includes,
      :conditions => to_conditions,
      :order => @order_by
    }
  end

  def include(path)
    unless @includes.include? path
      @includes << path
    end
  end
end