Encountering relationship issues with ActiveRecord has_many through on two different databases

Question

I'm trying to setup a has_many :through relationship over two different databases and encountering an issue.

My models are as follow:

company:

# this model lives in database main_db
class Company < ActiveRecord::Base
  has_many :company_custom_plans
  has_many :custom_plans, through: :company_custom_plans
end

custom_plan

# this model lives in database other_app_db
class CustomPlan < ActiveRecord::Base
  has_many :company_custom_plans
  has_many :companies, through: :company_custom_plans
end

joint model:

# this model lives in other_app_db
class CompanyCustomPlan < ActiveRecord::Base
  belongs_to :custom_plan
  belongs_to :company
end

### Schema ###
#  create_table "company_custom_plans", force: :cascade do |t|
#    t.integer "company_id",     limit: 4, null: false
#    t.integer "custom_plan_id", limit: 4, null: false
#  end

So it works quite great on the Company model but when trying to use this relation on the CustomPlan I'm getting an error because the has_many :through looks for the company_custom_plans in main_db instead of other_app_db

example:

ccp = CompanyCustomPlan.create!(company: company, custom_plan: custom_plan)
company.company_custom_plans == [ccp] # true
company.custom_plans == [custom_plan] # true
custom_plan.company_custom_plans == [ccp] # true

custom_plan.companies # error
ActiveRecord::StatementInvalid: Mysql2::Error: Table 'main_db.company_custom_plans' doesn't exist: SHOW FULL FIELDS FROM `company_custom_plans`

I tried to play with the different options of has_many :through (like setting source: 'company') but cannot figure out how to make this work.

Thanks,

Answer 1

I ended up figuring out how to force the has_many :through to use the right database following this stackoverflow answer.

class CompanyCustomPlan < ActiveRecord::Base

  self.table_name = "#{ActiveRecord::Base.connection.current_database}.company_custom_plans"

  belongs_to :custom_plan
  belongs_to :company
end

Answer 2

When naming join models for has_many through: associations (or models in general that have compound names) you should follow the format of SingularSingular for the model and singular_plural for the table. So you should name the model CompanyCustomPlan and the table company_custom_plans. This is different from has_and_belongs_to_many.

For example:

class User
  has_many :user_courses
  has_many :courses, though: :user_courses
end

class Course
  has_many :user_courses
  has_many :users, though: :user_courses
end

class UserCourse
  belongs_to :user
  belongs_to :course
end

This will correctly map the association to the UserCourse class. If we had used has_many :users_courses we would get NameError: uninitialized constant Users::Courses due to the way that ActiveRecord derives the class name from the association - plural words are interpreted as modules!

You can of course override this by providing the class_name option but following the convention may be a better idea in the first place unless you have a good reason not to.

You can of course use a use completely different name for the join model if there is something which describes the domain better - for example Enrollment instead of UserCourse.