Rails 3 Complex Join Query

I just struggled mightily to have the new Rails 3 query stuff create the SQL I wanted. I figured it out eventually. Here's the story.

I have the following models:

	class AddOn < ActiveRecord::Base
	has_many :pricing_plans, :dependent => :destroy
	end
	class PricingPlan < ActiveRecord::Base
	has_many :subscriptions
	end
	class Subscription < ActiveRecord::Base
	belongs_to :person
	belongs_to :pricing_plan
	end
	class Person < ActiveRecord::Base
	has_many :subscriptions
	end

view raw gistfile1.rb hosted with ❤ by GitHub

I wanted to add a method to Person that would return all the add-ons to which a person subscribed through payment plans:

	person = Person.first
	person.add_ons

view raw gistfile1.rb hosted with ❤ by GitHub

So, I knew I would need this SQL query:

	select * from add_ons
	inner join pricing_plans on add_ons.id = pricing_plans.add_on_id
	inner join subscriptions on pricing_plans.id = subscriptions.pricing_plan_id
	inner join people on people.id = subscriptions.person_id
	where people.id = 1

view raw gistfile1.sql hosted with ❤ by GitHub

I tried things like this, cursing the new query stuff the whole while:

	# i started with the following, which i thought would work with no problem:
	AddOn.joins(:pricing_plans).joins(:subscriptions)
	# returned this error:
	# ActiveRecord::ConfigurationError:
	# Association named 'subscriptions' was not found; perhaps you misspelled it?
	# trying to get at it from the other direction:
	PricingPlan.joins(:subscriptions).joins(:person)
	# of course this didn't work either
	# ActiveRecord::ConfigurationError: Association named 'person' was not found; perhaps you misspelled it?
	# here i shoot in the dark hoping something Arel-like would magically happen:
	AddOn.joins(:pricing_plans).joins(:subscriptions).on(pricing_plans[id].eq(subscriptions[pricing_plan_id]))
	# of course not. (this is embarrassing)
	# NameError: undefined local variable or method `pricing_plans' for #<Object:0x1001d52a8>

view raw gistfile1.rb hosted with ❤ by GitHub

They all failed. Finally, I got what I wanted:

	class Person
	# ...
	def add_ons
	AddOn.joins(:pricing_plans => {:subscriptions => :person}).where(:subscriptions => {:person_id => self})
	end
	# ...
	end

view raw gistfile1.rb hosted with ❤ by GitHub

It turned out to be nice and simple and I can do sweet chaining things like this:

	person = Person.first
	add_on = AddOn.first
	person.add_ons.exists?(add_on)

view raw gistfile1.rb hosted with ❤ by GitHub

Now, I love the new Rails 3 query stuff.