Hacking a subselect in ActiveRecord

October 29, 2008 Pivotal Labs

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.

About the Author

Biography

More Content by Pivotal Labs
Previous
Rake test error due to –trace argument
Rake test error due to –trace argument

We've had some trouble with test task errors causing failing builds on our continuous integration boxes eve...

Next
Vertebra
Vertebra

Ezra Zygmuntowicz talks about Vertebra, Engine Yard's distributed cloud application programming platform.

Enter curious. Exit smarter.

Learn More