Josh Susser's blog
This week, Damon and I were doing a performance optimization for some slow queries. The most performant solution involved denormalizing some data into a join table and doing a subselect to get the ids of the records we wanted. Not rocket science, but also a bit ugly to construct the SQL by hand. Our solution was to cheat a tiny bit and use an ActiveRecord internal method to generate the SQL for us.
def favorite_posts(options={})
subselect = Favorite.send(
:construct_finder_sql,
:select => "post_id",
:conditions => {:blog_id => self.id},
:order => "published_at DESC",
:limit => options[:limit] || 10, :offset => options[:offset])
Post.find(:all, :conditions => "posts.id IN (#{subselect})", :order => "published_at DESC")
end
That code uses the private method Favorite.construct_finder_sql to generate the following SQL:
SELECT * FROM posts WHERE posts.id IN (
SELECT post_id FROM favorites WHERE blog_id = 42 ORDER BY published_at DESC LIMIT 10 OFFSET 10
) ORDER BY published_at DESC
The Ruby may look like more code than the SQL, and in that form it is... but if you go the hack up a string route, once you start using string operations or interpolation to deal with the variable parts of the query it gets ugly pretty fast. Using the ActiveRecord code to put it all together keeps it nice and clean, and even makes sure things are sanitized and quoted properly too.