多态关联与求和操作引发的N+1查询问题

我的模型代码：

class FoodOrder < ApplicationRecord
    belongs_to :client
    has_many :order_items, dependent: :destroy, as: :order
    has_many :orderables, through: :order_items, source: :orderable, source_type: "Product"

    def productsOfType(type_of_product)
        orderables.where(category: type_of_product).sum(:quantity)
    end

    def total_items
        return productsOfType('general') + productsOfType('is_limited') + productsOfType('nuzest')
    end
end

我尝试预加载关联：

client = Client.includes(food_orders: [:order_items, :orderables]).find(params[:client_id])

但调用:total_items方法时，仍然出现N+1查询：

client.food_orders.map {|food_order| food_order.total_items}

# 响应日志
   (24.5ms)  SELECT SUM(quantity) FROM "products" INNER JOIN "order_items" ON "products"."id" = "order_items"."orderable_id" WHERE "order_items"."order_id" = $1 AND "order_items"."order_type" = $2 AND "order_items"."orderable_type" = $3 AND "products"."category" = $4  [["order_id", 17441], ["order_type", "FoodOrder"], ["orderable_type", "Product"], ["category", 0]]
   (24.2ms)  SELECT SUM(quantity) FROM "products" INNER JOIN "order_items" ON "products"."id" = "order_items"."orderable_id" WHERE "order_items"."order_id" = $1 AND "order_items"."order_type" = $2 AND "order_items"."orderable_type" = $3 AND "products"."category" = $4  [["order_id", 17441], ["order_type", "FoodOrder"], ["orderable_type", "Product"], ["category", 1]]
   (24.1ms)  SELECT SUM(quantity) FROM "products" INNER JOIN "order_items" ON "products"."id" = "order_items"."orderable_id" WHERE "order_items"."order_id" = $1 AND "order_items"."order_type" = $2 AND "order_items"."orderable_type" = $3 AND "products"."category" = $4  [["order_id", 17441], ["order_type", "FoodOrder"], ["orderable_type", "Product"], ["category", 3]]
   (23.9ms)  SELECT SUM(quantity) FROM "products" INNER JOIN "order_items" ON "products"."id" = "order_items"."orderable_id" WHERE "order_items"."order_id" = $1 AND "order_items"."order_type" = $2 AND "order_items"."orderable_type" = $3 AND "products"."category" = $4  [["order_id", 18917], ["order_type", "FoodOrder"], ["orderable_type", "Product"], ["category", 0]]
   (23.9ms)  SELECT SUM(quantity) FROM "products" INNER JOIN "order_items" ON "products"."id" = "order_items"."orderable_id" WHERE "order_items"."order_id" = $1 AND "order_items"."order_type" = $2 AND "order_items"."orderable_type" = $3 AND "products"."category" = $4  [["order_id", 18917], ["order_type", "FoodOrder"], ["orderable_type", "Product"], ["category", 1]]
   (23.6ms)  SELECT SUM(quantity) FROM "products" INNER JOIN "order_items" ON "products"."id" = "order_items"."orderable_id" WHERE "order_items"."order_id" = $1 AND "order_items"."order_type" = $2 AND "order_items"."orderable_type" = $3 AND "products"."category" = $4  [["order_id", 18917], ["order_type", "FoodOrder"], ["orderable_type", "Product"], ["category", 3]]

请问有没有办法解决这个问题？

解决方法 #

方案1：内存中计算聚合值 #

问题核心是sum(:quantity)会直接触发SQL查询，哪怕已经预加载了关联数据。改成用预加载后的集合在内存里统计：

修改FoodOrder模型方法：

class FoodOrder < ApplicationRecord
    belongs_to :client
    has_many :order_items, dependent: :destroy, as: :order
    has_many :orderables, through: :order_items, source: :orderable, source_type: "Product"

    def productsOfType(type_of_product)
        # 用预加载的orderables集合过滤后求和，不触发额外SQL
        orderables.select { |p| p.category == type_of_product }.sum(&:quantity)
    end

    def total_items
        productsOfType('general') + productsOfType('is_limited') + productsOfType('nuzest')
    end
end

只要预加载了orderables，所有计算都在内存完成，不会产生额外查询。

方案2：查询阶段预计算聚合值（推荐） #

如果数据量较大，内存计算效率不足，可以在查询阶段就把每个订单的各类产品数量计算好：

查询Client时用聚合语句一次性获取数据：

client = Client.find(params[:client_id])
client.food_orders = FoodOrder
  .where(client_id: client.id)
  .left_joins(order_items: :orderable)
  .where(order_items: { orderable_type: 'Product' })
  .group('food_orders.id')
  .select(
    'food_orders.*',
    'SUM(CASE WHEN products.category = \'general\' THEN order_items.quantity ELSE 0 END) AS general_count',
    'SUM(CASE WHEN products.category = \'is_limited\' THEN order_items.quantity ELSE 0 END) AS is_limited_count',
    'SUM(CASE WHEN products.category = \'nuzest\' THEN order_items.quantity ELSE 0 END) AS nuzest_count'
  )

修改模型方法直接取预计算属性：

def total_items
  general_count + is_limited_count + nuzest_count
end

这种方式仅需一次查询就能拿到所有聚合数据，性能最优。

方案3：使用数据库视图 #

如果这类聚合查询频繁，可以创建数据库视图固化关联逻辑。比如创建food_order_product_summary视图，包含food_order_id、general_count、is_limited_count、nuzest_count字段，然后在FoodOrder中添加has_one :product_summary关联，查询时直接预加载该关联即可。

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