:has_many Associations with Complex Joins (Kind of)

duck-vaderIn this episode, your fearless Jedi delves within the dark tree of associations, with_scope, inner joins, duck typing and meta-programming all in an effort to keep DRY…

In Joyomi I create an Omi when I lend you a book. I can optionally share that Omi with you. If I do, then the Omi shows up in the “Omis Shared With Me” tab in your dashboard. Sharing. Mkay. Social networking 101.

Now the query for a Joyomi user to find his own Omi’s (one’s he’s created) is trivial. The user model simply has_many omis. No brainer. On the other hand, going in the opposite direction and finding all the Omi’s that my friends have shared with me is decidedly non-trivial. In fact it’s downright tricky. It involves a big long series of joins. This is further complicated by the fact that in order to address potential SPAMming we don’t show you Omi’s that were created by folks who are not in your contact list. Got that? Good.

So I set out to implement the “Omis Shared With Me” tab hoping to reuse a lot of stuff from the “Outstanding Omis” and “Archived Omis” tabs. In particular I’ve got a nice clean little pagination routine that takes an association as a parameter and then proceeds to paginate the Omis in that association. Here’s the routine:

    1   def paginate_omis( association, order)
    2     @omis_pages = Paginator.new self, association.count, 10, params[:page]
    3     @omis = association.find(
    4       :all,
    5       :order => order,
    6       :limit => @omis_pages.items_per_page,
    7       :offset => @omis_pages.current.offset)
    8   end

(the order parameter lets you send in nice little SQL ordering clauses like ‘-expected_at DESC’ ) You can see this routine relies on the association parameter responding to count and find. Since “outstanding” and “archived” Omis are associations:

    1   has_many :outstanding_omis, :class_name => 'Omi', :conditions => "`archived_at` is null"
    2   has_many :archived_omis, :class_name => 'Omi', :conditions => "`archived_at` is not null"

This works fine. So when it came time to implement “Omis Shared With Me” I thought great, I’ll just add another association for that. Granted this is a more complex association, but from reading about the :joins option to ActiveRecord::Base#find it seemed doable. I’d just use that :joins option on my new association and everything would Just Workâ„¢. Boy was I wrong.

You may wonder why I would think that just because :joins was documented under find, that it would also work for has_many. Well it’s because those two methods mostly implement the same options.

Options Implemented by Both find and has_many

  • :conditions
  • :order
  • :group
  • :limit
  • :offset
  • :include
  • :select

But as it turns out there are a handful of discrepancies. Most of the discrepancies make sense. For instance, it wouldn’t make sense for find to support a :foreign_key option. But there are a few discrepancies that do not really make sense.

Options Implemented by find but not by has_many

  • :joins
  • :readonly
  • :lock

Hum, it seems to me that all three of these options would be useful on has_many. I groveled around the Rails source and I don’t see any particular reason why these aren’t supported by has_many. In fact it turns out that the find and has_many sources have many similarities. They just happen to have very little reuse going on.

It occured to me while reading the Rails source and seeing all the duplication between find and associations that there was a missing class, perhaps it should be called a “relation”. I’d like to instantiate a “relation” as a first class object. If such an object existed then both find and has_many could certainly use one. But that is the subject of a different post. I digress.

So finding myself in need of an association with :joins I did what any self-respecting Ruby programmer would do — I made one myself. Now before you freak out, I didn’t implement all 23-odd methods of has_many associations. I implemented only the two I needed in my pagination method (find and count). Remember, in Ruby, if it quacks like a duck that’s good enough. And I knew exactly which duck sounds this “association” would be called upon to emit.

So here is what the has_many would look like if has_many actually supported :joins:

    1 has_many :omis_shared_with_me, :class_name => 'Omi', :joins => 'inner join identities as i2 on i2.id = omis.identity_id inner join email_addresses as e1 on e1.address = i2.name inner join contacts as c1 on c1.id = e1.contact_id inner join contacts as c2 on omis.counterparty_id = c2.id inner join email_addresses as e2 on c2.id = e2.contact_id', :conditions => 'identities.id = c1.owner_id and e2.address = identities.name'

My workaround was to define a method on the user class (model). That method would return an instance of my new “association” class. Here is the method:

    1   def omis_shared_with_me( *args)
    2     proxy = Object.new
    3     instance_eval <<END
    4     class <<proxy
    5       def find( *args)
    6         with_scope do
    7           Omi.find( :all, *args)
    8         end
    9       end
   10       def count( *args)
   11         with_scope do
   12           Omi.count( *args)
   13         end
   14       end
   15       private
   16       def with_scope
   17         # without :select omis.* you get superfluous columns an the Omi objects aren't right (description is often NULL)
   18         Omi.with_scope(:find => { :select => 'omis.*', :joins => 'inner join identities as i2 on i2.id = omis.identity_id inner join email_addresses as e1 on e1.address = i2.name inner join contacts as c1 on c1.id = e1.contact_id inner join identities as i1 on i1.id = c1.owner_id inner join contacts as c2 on omis.counterparty_id = c2.id inner join email_addresses as e2 on c2.id = e2.contact_id', :conditions => ['e2.address = i1.name and :identity_id = i1.id', {:identity_id => #{id}}]}) do
   19           yield
   20         end
   21       end # def
   22     end # <<proxy
   23 END
   24     proxy
   25   end # def

This code bears some explanation. (darn tootin!) The most obvious way to define the association class would be to simply define a new Ruby class at the top level or maybe within the scope of the user class. If we did that however, we wouldn’t have access, in instances of that class, to the id attribute of the user. And we need that id when the association is called upon to do its thing.

Now we could add an instance variable to our association class to hold the id and we could initialize that thing on construction but that wouldn’t be very educational now would it? Nope, no instance variables for us — we’re going to use a little meta-programming instead.

So instead of defining the new class at the top level or in the context of the user class, we define an object-specific class (specific to the proxy object) and we evaluate that definition in the context of the user object. In this way, the reference to id in the private with_scope method will reference the id attribute of the appropriate user at runtime.

So that’s it. We are leveraging find to create an association class that supports the :joins option in 25 lines of code. This approach could easily be extended to add some of the other methods of has_many depending on your project needs. Also, I’m sure some Ruby Rock Star could DRY it up more.

The Thing With Two HeadsOn the other hand, doing a whole lot more to turn this hematoma into a second head is not a very wise use of resources. It’d be interesting to go in and steal (factor out) the best bits of find and has_many and build that general purpose ‘relation’ class I spoke of earlier, DRY-ing up find and associations, normalizing the options between them and giving us first-class ‘relations’ to boot. Anyone of you ActiveRecord warriors out there interested in some deep hacking?

This entry was posted in Ruby on Rails, SQL. Bookmark the permalink.

One Response to :has_many Associations with Complex Joins (Kind of)

  1. Bill says:

    Rails ticket 2840 proposes a :distinct option for has_many. And a certain Ryan Carver weighs in in the comments with a proposal for adding support for :joins. From his comment:

    I’ve been using a custom read only has_many implementation I called “knows_about” for supporting these more complex associations.. because once you start doing joins it’s really a different thing than has_many.

    That read only part sure makes sense.

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