Multiple Foreign Keys In Rails…
November 12th, 2008So yesterday at work, I ran into a seriously interesting problem and its the first time in the three years I’ve been working with Rails that I’ve really butted heads with ActiveRecord. I’m not really sure if ActiveRecord won or I did, but I eventually got it to work. Luckily, I can blame most of the problem on will_paginate and my own laziness.
Here is the scenario: I’ve got a model, lets call it Items. Now Item belongs to Users by the way of user_id. However it is also possible for different User to temporarily rent an Item, this is represented using renter_id.
Now in Rails this is pretty straight forward:
class Item < ActiveRecord::Base
belongs_to :user
belongs_to :renter, :class_name => “User”
end
class User < ActiveRecord::Base
has_many :my_items, :class_name => “Item”, :foreign_key => “user_id”
has_many :rented_items, :class_name => “Item”, :foreign_key => “renter_id”
end
No problem, right? Well, what if i wanted a list of all items that a User has access to. I basically want a mySQL select of this:
SELECT * FROM items WHERE (items.user_id = user.id or items.renter_id = user.id)
What would I like to do is this:
has_many :items, :foreign_key => ["user_id", "renter_id"]
Unfortunately that was not to be. So my next idea was just to use a method that gathers both and passes it back. For example:
def items
my_items + rented_items
end
However, this is gloriously inefficient, requiring the entire collection to be gathered from the database then chopped up and paginated in Ruby. Bad idea… Besides, will_paginate doesn’t like working that way (which is a good thing)
After beating my head against rails and mySQL and trying to remember which comes first AND or OR. I finally broke down and decided to implement it old school using :finder_sql, which ended up looking something like this:
has_many :items, :finder_sql => 'SELECT * FROM items WHERE (items.user_id = #{id} or items.renter_id = #{id})'
(And yes the single quotes are quite important in this context because you want the literal string ‘#{id}’ to be evaluated later, not when the string is generated.)
And this will work fine for things like User.find(:first).items, and I was happy. Until I tried to do User.find(:first).items.paginate and it freaked the hell out (being unable to build a proper request from the finder_sql which is totally understandable). And again I fell into despair and hit up the rails api. And lo and behold I found the :extend key on has_many. What it allows you to do is define custom methods, usually finders or creators for the assocation, using the proxy reflection data that it provides it was “easy-ish” to accomplish what I wanted to do, by overwriting the paginate method from will_paginate and build the Pagination collection by hand, and the controller programmer is none the wiser that I have totally hijacked the ball.
It ended up looking something like this:
has_many :items, :finder_sql => 'SELECT * FROM items WHERE (items.user_id = #{id} or items.renter_id = #{id})' do
def paginate(page, per_page)
total_entries = proxy_reflection.class_name.constantize.count_by_sql proxy_reflection.options[:counter_sql].gsub(’#{id}’, proxy_owner.id.to_s)
WillPaginate::Collection.create(page || 1, per_page || 10, total_entries) do |pager|
pager.replace proxy_reflection.class_name.constantize.find_by_sql(proxy_reflection.options[:finder_sql].gsub(’#{id}’, proxy_owner.id.to_s) + ” LIMIT #{pager.per_page} OFFSET #{pager.offset}”)
end
end
end
Finally!
