Building Queries Programmatically

Problem

You have to write fragments of SQL to pass parameters into an ActiveRecord query. Youd like to dispense with SQL altogether, and represent the query paramaters as a Ruby data structure.

Solution

Heres a simple solution. The method ActiveRecord::Base.find_by_map defined below picks up where find leaves off. Normally a query is represented by a SQL fragment, passed in as the :conditions argument. Here, the :conditions argument contains a mapping of database field names to the desired values:

require cookbook_dbconnect class ActiveRecord::Base def self.find_by_map(id, args={}.freeze) sql = [] values = [] args[:conditions].each do |field, value| sql << "#{field} = ?" values << value end if args[:conditions] args[:conditions] = [sql.join( AND ), values] find(id, args) end end

Heres find_by_map in action, using the BlogPost class first seen in Recipe 13.11:

activerecord_connect class BlogPost < ActiveRecord::Base end BlogPost.create(:title => Game Review: Foosball Carnage, :content => Four stars!) BlogPost.create(:title => Movie Review: Foosball Carnage: The Movie, :content => ero stars!) BlogPost.find_by_map(:first, :conditions => {:title => Game Review: Foosball Carnage } ).content # => "Four stars!"

Discussion

ActiveRecord saves you from having to write a lot of SQL, but you still have to write out the equivalent of a SQL WHERE clause every time you call ActiveRecord::Base#find. The find_by_map method lets you define those queries as Ruby hashes.

But find_by_map only lets you run one type of query: the kind where you e restricting fields of the database to specific values. What if you want to do a query that matches a field with the LIKE construct, or combine multiple clauses into a single query with AND or OR?

A hash can only represent a very simple SQL query, but the Criteria object, below, can represent almost any WHERE clause. The implementation is more complex but the idea is the same. We define a data structure that can represent the WHERE clause of a SQL query, and a way of converting the data structure into a real WHERE clause.

Heres the basic class. A Criteria acts like a hash, except it maps a field name to a value and a SQL operator. Instead of mapping :title to Game Review: Foosball Carnage, you can map it to [%Foosball%, LIKE]. Each Criteria object can be chained to other objects as part of an AND or OR clause.

class Criteria < Hash def initialize(values) values.each { |k,v| add(k, *v) } @or_criteria = nil @and_criteria = nil end :private attr_accessor :or_criteria, :and_criteria :public def add(field, value, operation==) self[field] = [value, operation] end def or(criteria) c = self while c.or_criteria != nil break if c == criteria c = c.or_criteria end c.or_criteria = criteria return self end def and(criteria) c = self while c.and_criteria != nil break if c == criteria c = c.and_criteria end c.and_criteria = criteria return self end

This method turns a Criteria object, and any other objects to which its chained, into a SQL string with substitutions, and an array of values to use in the substitutions:

class Criteria def to_where_clause sql = [] values = [] each do |field, value| if value.respond_to? :to_str value, operation = value, = else value, operation = value[0..1] end sql << "#{field} #{operation} ?" values << value end sql = ( + sql.join( AND ) + ) if or_criteria or_where = or_criteria.to_where_clause sql = "(#{sql} OR #{or_where.shift})" values += or_where end if and_criteria and_where = and_criteria.to_where_clause sql = "(#{sql} AND #{and_where.shift})" values += and_where end return values.unshift(sql) end end

Now its simple to write a version of find that accepts a Criteria:

class ActiveRecord::Base def self.find_by_criteria(id, criteria, args={}.freeze) args = args.dup args[:conditions] = criteria.to_where_clause find(id, args) end end

Heres Criteria used to express a complex SQL WHERE clause with a little bit of Ruby code. This query searches the blog_post table for reviews of bad movies and good games. The movies and the games must not be about the game of cricket.

review = Criteria.new(:title => [\%Review%, LIKE]) bad_movie = Criteria.new(:title => ["%Movie%", LIKE], :content => ero stars!) good_game = Criteria.new(:title => [\%Game%, LIKE], :content => Four stars!) no_cricket = Criteria.new(:title => [\%Cricket%, NOT LIKE]) review.and(bad_movie.or(good_game)).and(no_cricket) review.to_where_clause # => ["((title LIKE ?) AND # (((content = ? AND title LIKE ?) OR (content = ? AND title LIKE ?)) # AND (title NOT LIKE ?)))", # "%Review%", "Zero stars!", "%Movie%", "Four stars!", "%Game%", # "%Cricket%"] BlogPost.find_by_criteria(:all, review).each { |post| puts post.title } # Game Review: Foosball Carnage # Movie Review: Foosball Carnage: The Movie

The technique is a general one. Its easier for a human to construct Ruby data structures than to write valid SQL clauses, so write code to convert the one into the other. You can use this technique wherever any library expects you to write SQL.

For instance, the find method expects SQL fragments representing a querys ORDER BY or GROUP BY clause. You could represent each as an array of fields, and generate the SQL as needed.

# Just an idea… order_by = [[:title, ASC]]

See Also

Related

Категории