是否可以在Rails中跨多个数据库进行内部联接？ [英] Is it possible to inner join across multiple databases in Rails?

问题描述

我很难通过 has_many：through 关联访问数据，其中某些表位于单独的数据库中。

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

似乎无法在两个数据库中进行 INNER JOIN 吗？有什么我可以做的事来缓解吗？尝试将此方法添加到 AbstractClass 中，但不幸的是它没有解决任何问题：

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

推荐答案

我不认为可以合并两个在一个查询中使用不同的表。您可能可以做的是使用Ruby获得最终收藏。
使用一个查询从一个数据库中获取集合，然后从另一个查询中获取另一个集合。
然后使用Ruby从这两个集合中进行选择/过滤。希望对您有帮助。

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.

这篇关于是否可以在Rails中跨多个数据库进行内部联接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！