ruby-on-rails - 避免 Rails 多表查询中的 N+1 查询

Question

这是我目前得到的查询:

SELECT t1.discipline_id AS discipline1,
  t2.discipline_id AS discipline2,
  COUNT(DISTINCT t1.product_id) as product_count
FROM (SELECT "product_disciplines".* FROM "product_disciplines") t1
INNER JOIN (SELECT "product_disciplines".* FROM "product_disciplines") t2
ON t1.product_id = t2.product_id
WHERE (t1.discipline_id < t2.discipline_id)
GROUP BY t1.discipline_id, t2.discipline_id
ORDER BY "product_count" DESC

基本上，我有一个产品和学科列表，每个产品可能与一个或多个学科相关联。这个查询让我可以计算出，对于每个可能的(不同的)规程对，有多少产品与它们相关联。我将使用它作为 dependency wheel in Highcharts 的输入.

当我涉及到事件模型序列化程序时，问题就出现了。这是我的 Controller :

class StatsController < ApplicationController
  before_action :get_relationships, only: [:relationships]

  def relationships
    x = @relationships
      .select('t1.discipline_id AS discipline1, t2.discipline_id AS discipline2, COUNT(DISTINCT t1.product_id) as product_count')
      .order(product_count: :DESC)
      .group('t1.discipline_id, t2.discipline_id')
    render json: x, each_serializer: RelationshipSerializer
  end

  private

  def get_relationships
    query = ProductDiscipline.all

    @relationships = ProductDiscipline
      .from(query, :t1)
      .joins("INNER JOIN (#{query.to_sql}) t2 on t1.product_id = t2.product_id")
      .where('t1.discipline_id < t2.discipline_id')
  end
end

each_serializer 指向这个类:

class RelationshipSerializer < ApplicationSerializer
  has_many :disciplines do
    Discipline.where(id: [object.discipline1, object.discipline2])
  end
  attribute :product_count
end

当我查询数据库时，有大约 1300 个可能的对，这将我的单个查询转换为大约 1300 个学科查找。

有没有办法避免这种结构的 N+1 查询问题？

Answer 1

我最终将其拆分为两个单独的 API 查询。 RelationshipSerializer 只保存规程 ID，

class RelationshipSerializer < ApplicationSerializer
  # has_many :disciplines do
  #   # Discipline.where(id: [object.discipline1, object.discipline2])
  #   [object.discipline1, object.discipline2].to_json
  # end
  attributes :discipline1, :discipline2
  attribute :product_count
end

因为在我的应用程序中我已经需要可用学科的列表，所以我选择将它们关联到客户端。