Is it possible to inner join across multiple databases in Rails?

Question

I'm having difficult accessing data with a has_many :through association where some of the tables live in a separate database.

# database_one
class Input < ApplicationRecord
  belongs_to :user      # Works great
end

# database_two
class User < AbstractClass
  belongs_to :group     # Works great
  has_many :inputs      # Works great
end

# database_two
class Group < AbstractClass
  has_many :users                     # Works great
  has_many :inputs, through: :users   # Does not work at all
end

class AbstractClass < ApplicationRecord
  self.abstract_class = true
  establish_connection "database_two_#{Rails.env}".to_sym
end

So with the code as it is above, I can do the following:

Group.first
=> #<Group id: 1...

User.first
=> #<User id: 1...

User.first.inputs
=> #<ActiveRecord::Associations::CollectionProxy []>

Group.first.users
=> #<ActiveRecord::Associations::CollectionProxy []>

But it won't let me do the following:

Group.first.inputs
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "users" does not exist
LINE 1: SELECT  "inputs".* FROM "inputs" INNER JOIN "users" ON "inpu...
                                                ^
: SELECT  "inputs".* FROM "inputs" INNER JOIN "users" ON "inputs"."user_id" = "users"."id" WHERE "users"."group_id" = $1 LIMIT $2

It looks like it's not possible to do an INNER JOIN across two databases? Is there anything I can do to alleviate this? I've tried adding this method to the AbstractClass but it didn't solve anything unfortunately:

def self.table_name_prefix
  "database_two_#{Rails.env}."
end

As a workaround, I have added the following to the Group model, but this isn't the solution I'm looking for.

def inputs
  Input.where(id: users.ids)
end

Answer 1

I don't think it is possible to join two different tables in one query. What you can probably do instead is use Ruby to get your final collection. Get the collection from one DB with one query and then another collection from the other query. Then use Ruby to select/filter from these two collections. I hope this helps you.