Recipe 13.13. Building Queries Programmatically
Problem
You have to write fragments of SQL to pass parameters into an ActiveRecord query. You'd like to dispense with SQL altogether, and represent the query paramaters as a Ruby data structure.
Solution
Here's 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
Here's 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 => 'Zero 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're 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.
Here's 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 it's 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 it's 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
Here's 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 => 'Zero 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. It's 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 query's 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
| 
