has_many :finder_sql

SQL can be a beautiful thing. That being said, I do try to avoid using native SQL as much as possible when writing applications, because for non-DBA types, it really doesn’t have the most readable syntax in the world.

In ActiveRecord Models, I make heavy use of Models to represent Join tables – this isn’t necessary because of the has_and_belongs_to_many association, however, has_and_belongs_to_many can be very problematic to create Observers for (especially with the limited capabilities of the :after/before_add/remove set of callbacks). In most cases, a HABTM relationship can be defined using only has_many relationships and the :through parameter. Take, for instance, the following representation:

 class ZooTycoon < ActiveRecord::Base

Let’s think of the relationships here – a ZooTycoon owns many Zoos, which themselves contain Cages, which have Animals which belong in the cage. Using a minimalist approach, how do we attack this problem such that we can build logical relationships between the different classes? We’d probably do something like this:

 class ZooTycoon < ActiveRecord::Base
	has_many :zoos
 class Zoo < ActiveRecord::Base
        belongs_to :zoo_tycoon
	has_many :cages
class Cage < ActiveRecord::Base
	has_many :animals
	belongs_to :zoo
class Animal < ActiveRecord::Base
	belongs_to :cage

Humanitarian concerns aside, this is more or less an accurate representation of how the classes are represented. Now, in our application, we can do the following to find out all the animals a particular ZooTycoon owns:

joey = ZooTycoon.find_by_name('Joey')
zoos = joey.zoos
cages = zoos.collect {|zoo| zoo.cages}
animals =  (cages.flatten.each {|cage| cage.animals }).flatten

Bear with me, I know there are about a million ways to shorthand this code. What a freakin’ pain! Luckily, we can use the has_many :through association to make this a bit simpler. Let’s redefine zoos to be able to retrieve animals directly, rather than having to iterate through all the cages:

 class Zoo  :cages,
                       :source => :animals

What does this tell our application? It tells us that we don’t need to create a join table between Zoos and Animals; we already have one! The Cages. We can effectively call the following:


Yeah, this is nice, but what if we wanted to directly find out all the animals Joey owned? Well, naturally we’d assume we could do this:


 class ZooTycoon < ActiveRecord::Base
	has_many :zoos
        has_many :animals, :through => :zoos

It breaks. ActiveRecord is pretty smart, but ActiveRecord isn’t smart enough to correctly create 3 JOINs. If we take a look at the log, we realize that ActiveRecord is trying to directly join the ZooTycoon with the Zoo with the Animals, bypassing the Cages completely. What do we do? Looks like finder_sql to the rescue. SQL can be ugly, but SQL can also be a beautiful thing when you need something very specific:

 class ZooTycoon  'Animal',
                       :finder_sql = 'SELECT * FROM zoo_tycoons
                                           JOIN zoos ON zoo_tycoons.id = zoos.zoo_tycoon_id
                                           JOIN cages ON zoos.id = cages.zoo_id
                                           JOIN animals ON animals.cage_id = cages.id
                                           WHERE zoo_tycoon.id = #{id}'


NOTE THE SINGLE QUOTES. Note the single quotes. Note the single quotes. You MUST use single quotes. To understand why this is, you have to understand that the ‘configuration’ methods in ActiveRecord models are actually function calls that get executed before any Models are actually instantiated. Finder_sql is effectively a function that goes something like, set_some_value_to(‘#{self.id}’) that gets called before any Objects are created, so the instance variable @some_value is set to ‘#{self.id}’ rathern than the Object id of the class, which will probably be some ridiculous 11 digit number rather than the Model’s ID. By passing a single quoted parameter, we ensure that the #{id} value substitution happens during an Object’s lifecycle.

What does this mean for us? We can now call:


What’s totally awesome about this is that now we get the entire suite of ActiveRecord methods and callbacks for free! What if Joey wants his animals to be destroyed when he is destroyed? We just append a :dependent => :destroy.

Now go out there and write some Ruby.

About these ads

6 responses to “has_many :finder_sql

  1. This only works as long as you don’t use eager loading with :include. The moment you do this AR will ignore the :finder_sql you have defined and throw an error.

  2. Pingback: Space Babies » Blog Archive » Note the single quotes!

  3. Pingback: this is totally gonna work… » Blog Archive » ActiveRecord, Associations and Counters

  4. Thank you for the single-quotes warning. This issue pushed me on the wrong track for several hours till I came upon your post.

  5. I don’t understand some of the syntax here. Why is it that you are opening these classes again, like “class Zoo :cages, :source => :animals” what is going on here in terms of Ruby syntax? And in the second one, is that :finder_sql supposed to be followed by => rather than =?

  6. Pingback: Josh's Blog » Blog Archive » has_many :finder_sql

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