Just listen to Alex

December 18, 2007

Object-oriented approach to ActiveRecord#find

Filed under: programming — Tags: , — bosmeeuw @ 7:44 pm

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&#91;:filter_category&#93;
	end

	if params&#91;:only_in_stock&#93; == "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&#91;:filter_category&#93;
	end

	if params&#91;:only_in_stock&#93; == "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[/sourcecode]

Advertisements

8 Comments »

  1. I think the HasFinder gem is a lifesaver in this scenario:

    http://pivots.pivotallabs.com/users/nick/blog/articles/284-hasfinder-it-s-now-easier-than-ever-to-create-complex-re-usable-sql-queries

    This also has the advantage of allow us to define the SQL inside the Class, and not in the controller. This is how we could conceivably solve the problem presented in your first example:

    class Product ‘stock > 0’
    has_finder :filter_category, lambda {|category| { :conditions => {:category => category} } }
    end

    Now in your controller method:

    def list_items
    @products = Product.in_stock
    @products = @products.filter_category(params[:filter_category]) if params[:filter_category]
    @products.find(:all, :limit => 10)
    end

    The gorgeous thing about this code is that has_finder can operate on an Association Proxy, and returns an Association Proxy – no only can you repeat this pattern indefinitely with more finders, you can call a find() method even at the end of the method, conceivably to work with paginating_find!

    Comment by Ikai Lan — December 22, 2007 @ 8:02 am

  2. Hello Ikai,

    WordPress seems to have eaten some of your comment but I get the idea. I didn’t know about this gem, it’s prettier than my implementation indeed!

    The only thing I’m wondering about is inheritance. Imagine you have a bunch of models for a simple website, like NewsItem, PageItem, JobItem, etc.. You want to filter all online news. An online filter based on an a checkbox “online” is defined for all three models. An additional online filter is defined for NewsItem: the “publication_date” must be today or earlier. I would solve this by creating a WebItemFinder with a method “online”, and a subclass NewsItemFinder which overload this “online” method by calling the super (WebItemFinder#online) first and then adding its own condition. Is this possible with has_finder?

    Note that this is probably academic for most people, I think your implementation would be more suitable than mine in most cases.

    Comment by bosmeeuw — December 22, 2007 @ 10:53 pm

  3. looks very similar to this plugin:
    http://code.google.com/p/railswhere/

    what do u think? maybe u can join forces with Tim?

    Comment by Andre P. — December 24, 2007 @ 5:16 pm

  4. You’re looking for a solution to a nonexistent problem.

    Instead of

    # if params[:filter_category] != “”
    # conditions << “category_id = ?”
    # conditions_parameters << params[:filter_category]
    # end

    just do:

    # conditions = ” ….. AND (? IN (”, category_id))”, params[:filter_category]

    Well, you get the idea. And none of this crazy overhead you propose.

    Comment by Wesha — December 24, 2007 @ 7:07 pm

  5. Wesha, seeing that there are several other projects solving this problem, I doubt that it is nonexistent. I don’t see how your snippet solves the problem of dynamically building conditions, much less centralizing conditions for re-use through controllers.

    Andre P: thanks for the link, this one also looks interesting!

    Comment by bosmeeuw — December 26, 2007 @ 12:05 pm

  6. The SearchAPI plugin (http://www.pierlis.com/blog/2007/12/10/release-of-the-searchapi-plugin) is an alternative to HasFinder.

    Both allows you to define your search keys inside the model class itself, but instead of defining proxies, special methods, SearchAPI extends the condition Hashes natively supported by ActiveRecord. It plays well with associations, too.

    Comment by Gwendal Roué — December 30, 2007 @ 3:33 pm

  7. […] One of my colleges (the main programmer for the company I work for) has created a nice post on creating dynamic search criteria in an object-oriented way using Rails ActiveRecord. You can find his post here. […]

    Pingback by Object-oriented approach to ActiveRecord | Lone Gunman — July 27, 2008 @ 6:36 pm

  8. Yes! Finally something about long travel 2wd tacoma.

    Comment by frislo travel 3way backpack — March 30, 2014 @ 2:19 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: