How to join three tables in Rails?

Question

I have my records set up as follows

Subscription: id, plan_id                 <- belongs to plan
Plan: id, role_id                         <- belongs to role
Role: id, name

Given this data

Subscription: id: 1, plan_id: 5
Plan: id: 5, role_id: 10
Role: id: 10, name: 'Gold'

I'm trying to write a join so that I can find subscriptions based on their associated roles, i.e.:

Subscription.joins(:plan).joins(:role).where("roles.name = ?", 'Gold')

But this approach doesn't work. Any help would be appreciated.

Thanks.

Answer 1

If you have proper associations then use this:

Subscription.includes(:plan => [:role]).where("roles.name = 'Gold'").first

You can also write the query manually:

Subscription.joins("INNER JOIN plans ON plans.id = subscriptions.plan_id
                    INNER JOIN roles ON roles.id = plans.role_id").where("roles.name = 'Gold'").first

Answer 2

If you're trying to find all the subscriptions for a given role, why not start with the role? Rails can take care of these "one step removed" associations if we configure them correctly.

gold_role = Role.where(name: 'Gold').first
gold_role.subscriptions

This is possible via Rails' has_many :through relations. I think this is the correct setup for your models:

class Subscription < ActiveRecord::Base
  belongs_to :plan
  has_one :role, through: :plan
end

class Plan < ActiveRecord::Base
  has_many :subscriptions
  belongs_to :role
end

class Role < ActiveRecord::Base
  has_many :plans
  has_many :subscriptions, through: :plans
end