Rails 5.0中如何用has_many关联实现Project多Investor查询？

当然可行！在Rails 5.0里，我们完全可以利用ActiveRecord的关联特性来替代手动拼接ID的查询方式，既符合Rails的最佳实践，又能获得更高效的SQL查询。下面给你两种实用的实现方案：

方案一：拆分关联后通过UNION合并查询 #

先给Project分别定义两个has_many :through关联，对应两种中间表的Investor，再通过自定义方法合并结果：

class Project < ApplicationRecord
  # 关联通过Subscription表的Investor
  has_many :subscriptions
  has_many :subscription_investors, through: :subscriptions, source: :investor

  # 关联通过ExternalSubscription表的Investor
  has_many :external_subscriptions
  has_many :external_subscription_investors, through: :external_subscriptions, source: :investor

  # 自定义方法返回所有关联的Investor
  def investors
    # 用UNION合并两个关联的查询，避免重复
    Investor.where(id: subscription_investors.select(:id).union(external_subscription_investors.select(:id)))
  end
end

用法说明 #

直接调用project.investors就能拿到所有关联的Investor，而且返回的是ActiveRecord::Relation对象，支持继续链式调用（比如加where、order等条件），比手动拼ID的方式更灵活。

方案二：直接定义单个跨多中间表的has_many关联 #

如果你希望直接用project.investors这种原生关联语法，可以通过自定义SQL片段来实现：

写法一：用EXISTS子查询（性能更优） #

class Project < ApplicationRecord
  has_many :investors, ->(project) {
    where(
      "EXISTS (SELECT 1 FROM subscriptions WHERE subscriptions.investor_id = investors.id AND subscriptions.project_id = ?)
       OR EXISTS (SELECT 1 FROM external_subscriptions WHERE external_subscriptions.investor_id = investors.id AND external_subscriptions.project_id = ?)",
      project.id, project.id
    ).distinct
  }
end

写法二：用LEFT JOIN关联 #

class Project < ApplicationRecord
  has_many :investors, ->(project) {
    joins("LEFT JOIN subscriptions ON subscriptions.investor_id = investors.id")
      .joins("LEFT JOIN external_subscriptions ON external_subscriptions.investor_id = investors.id")
      .where("subscriptions.project_id = ? OR external_subscriptions.project_id = ?", project.id, project.id)
      .distinct
  }
end

注意点 #

两种写法都要加distinct，避免同一个Investor同时通过Subscription和ExternalSubscription关联时出现重复结果。

额外注意事项 #

1. 确保你的Subscription和ExternalSubscription模型已经正确设置了关联：

   # Subscription模型
   class Subscription < ApplicationRecord
     belongs_to :project
     belongs_to :investor
   end
   
   # ExternalSubscription模型
   class ExternalSubscription < ApplicationRecord
     belongs_to :project
     belongs_to :investor
   end
   

2. 数据库表需要有对应的外键字段：subscriptions.project_id、subscriptions.investor_id、external_subscriptions.project_id、external_subscriptions.investor_id，最好加上数据库外键约束来保证数据一致性。

内容的提问来源于stack exchange，提问作者yoones