ruby-on-rails 多态关联和求和的N+1问题

zengzsys  于 2023-06-25  发布在  Ruby
关注(0)|答案(5)|浏览(118)
    • bounty已结束**。回答此问题可获得+100声望奖励。赏金宽限期8小时后结束。Jeremy Thomas希望引起更多关注这个问题。

我在尝试对多态关联执行计算时遇到了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}

# response

   (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]]

有什么办法可以防止这种情况发生吗?

kyxcudwk

kyxcudwk1#

你试图从树干开始,然后顺着树枝数一数树叶。你的代码是说爬上树,然后去每一个分支。在rails中,您希望通过计数树叶并根据树干限定这些树叶来计数树叶。
我不确定我对你的模型是否正确…

types_of_interest = ['general', 'is_limited', 'nuzest']
Product.joins(order_items: :food_order).merge(FoodOrder.where(id: food_order.id)).where(category: types_of_interest).sum(:quantity)

抓住那些类型的产品,是该订单的一部分,并总和的数量。

wd2eg0qa

wd2eg0qa2#

你应该能够做这样的事情:

client.food_orders.select('food_orders.id, sum(quantity) as sum_quantity').group('food_orders.id')

您将不得不为您需要访问的任何其他字段添加其他字段到select子句中,但这是一个不错的起点。如果需要返回没有订单的客户端,可能还需要使用left_joins()。

xqnpmsa8

xqnpmsa83#

首先,Eager Loading的工作方式并不像您预期的那样。
让我们举一个例子:

# Eager loading orderables by includes
orders = client.food_orders.includes(:orderables)

# Way 1, will trigger extra queries as `sum(:filed_name)` method (From Rails) will generate SQL query
orders.map { |order| order.orderables.sum(:quantity) }

# Way 2, won't trigger extra queries as orderables already loaded
orders.map { |order| order.orderables.sum { |orderable| orderable.quantity } }

然后是你的代码。
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"

  # Will reduce queries to one third, but can't resolve N+1 as the above example stated
  def total_items
    orderables.where(category: ['general', 'is_limited', 'nuzest'])).sum(:quantity)
  end
end
  1. sum方法实际上更接近Product模型,所以我们可以将它移到那里:
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 total_items
    orderables.total_quantities
  end
end

class Product
  belongs_to :food_order

  scope :total_quantities, -> { where(category: ['general', 'is_limited', 'nuzest'])).sum(:quantity) }
end

1.我们想对Product模型做一些计算,所以我们可以解析N+1,而不是使用client.food_orders.map { ... },并简化代码为:

Product.joins(food_order: :client)
       .where(clients: { id: params[:client_id] })
       .total_quantities
jecbmhm3

jecbmhm34#

关于FoodOrder模型
productsOfType方法名与其主体没有关联,不符合Ruby约定。应该是这样的

def products_by_type(product_type)
  orderables.where(category: product_type)
end

如果需要对所有类别求和,则不需要显式调用它们。为什么不这样称呼他们:

food_order.orderables.sum(:quantity)

或者,如果你需要明确的一些类别,像这样:

food_order.products_by_type(%w[general is_limited nuzest]).sum(:quantity)

在这篇评论中,你写道:
food_orders = FoodOrder.includes(:order_items).joins(:client, {order_items: :product}).merge(Product.where(category: ['general', 'is_limited', 'nuzest'])).where(clients: {id: client.id})。然后我可以调用food_orders.map {|fo| fo.order_items} w/o命中DB,但food_orders.map {|fo| fo.order_items.sum(:quantity)}仍然这样做:(
你可以这样修

food_orders.sum { |fo| fo.order_items.quantity }

它是带块的sum,而不是带符号的sum

5us2dqdw

5us2dqdw5#

作为一般的答案,大多数ORM都有这个问题。ORM如何解析编写的代码有点像这样:
对于第一个查询中接收到的每一行,运行一个查询,获取该行中id的总和。
相反,您应该告诉ORM获取每个id或任何您想要求和的总和(可能是名称或产品SKU,这取决于上下文)。用更专业的术语来说,求和和分组的键,你希望求和给你一个正确的结果。
如果你的模型有关系,则inner在查询中连接这些关系,并将总和作为查询中的一个字段,并按必要的键分组。
这将导致一个查询,该查询从数据库获取数据,并在具有group by约束的数据集上运行聚合函数SUM
Ben Trewern提供了一个简单的例子来解释我在他的回答中所写的解释。
希望这些一般信息能解决您的n + 1问题。

相关问题