nuclearpanda.com

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
end

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
 end
 class Zoo < ActiveRecord::Base
        belongs_to :zoo_tycoon
	has_many :cages
 end
class Cage < ActiveRecord::Base
	has_many :animals
	belongs_to :zoo
 end
class Animal < ActiveRecord::Base
	belongs_to :cage
 end

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
 end

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:

joeys_zoos.animals

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:

DOES NOT WORK:

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

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}'

  end

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:

joey.animals

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.