ruby-on-rails - 按日期计算订单中特定系列的已售产品

Question

我正在尝试找出计算特定家庭订单在给定日期范围内售出产品的最佳方法。

这些是我的简化模型:

• 订单 placed_on
• OrderItem order_id, product_id, amount
• 产品 family_id
• 家庭

所以，现在给定一些日期，例如 d1 和 d2，我需要计算有多少 Product给定的 Family 都在那些 Order 中。

期望的输出应该是这样的:

# all these are products from the same family sold in the last week
[
  {"product_24": 3435},
  {"product_34": 566},
  {"product_83": 422}
  …
]

我知道如何遍历所有订单，但我认为应该有更好的方法。

Answer 1

假设您的数据模型和变量应该是这样的:

OrderItem.joins(:order)
         .joins(product: :family)
         .where(orders: {created_at: d1..d2})
         .where(products: {family_id: <YOUR_FAMILY_ID>})
         .group(:product_id)
         .sum(:amount)

这将生成以下 sql:

SELECT
    SUM("order_items"."amount") AS sum_amount,
    "order_items"."product_id" AS order_items_product_id
FROM "order_items"
    INNER JOIN "orders" ON "orders"."id" = "order_items"."order_id"
    INNER JOIN "products" ON "products"."id" = "order_items"."product_id" 
    INNER JOIN "families" ON "families"."id" = "products"."family_id" 
WHERE
    ("orders"."created_at" BETWEEN ? AND ?)
        AND "products"."family_id" = ?
GROUP BY "order_items"."product_id"

并返回以下结构:

=> [{product_id => <sum of this product id since d1 until d2 for family_id>}, ...]

此外，我假设您想对每种产品的数量求和。让我知道是否适合您。