运维开发网

ruby-on-rails – 在应用程序中跟踪产品销售并选择畅销产品的最佳方法是什么?

运维开发网 https://www.qedev.com 2020-05-25 20:33 出处:网络 作者:运维开发网整理
我已经设置了这样的数据库: 产品: # id :integer not null, primary key # code :string # name :string # category_id :integer ... 订单商品: # id :integer
我已经设置了这样的数据库:

产品:

#  id             :integer          not null, primary key
#  code           :string
#  name           :string
#  category_id    :integer
...

订单商品:

#  id              :integer          not null, primary key
#  order_id        :integer
#  product_id      :integer
#  color_id        :integer
#  qty             :integer          default("0")
#  price           :money
...

订单:

#  id                  :integer
#  state               :string
#  placed_on           :datetime
...

现在这个设置让我很难从每个类别中每周选择最畅销的产品.我怎样才能解决这个问题?另一个跟踪销售情况的数据库?请帮忙.

您基本上需要的是加入类别,产品,order_items和订单表.

可以使用以下代码完成连接:

rel = Category.joins(products: [order_items: :order])
#=> SELECT "categories".* FROM "categories" INNER JOIN "products" ON "products"."category_id" = "categories"."id" INNER JOIN "order_items" ON "order_items"."product_id" = "products"."id" INNER JOIN "orders" ON "orders"."id" = "order_items"."order_id"

基于此,您可以过滤日期间隔.

假设我们已经有了d1和d2值,它们定义了我们感兴趣的区间的开始和结束:

rel = rel.where('orders.placed_on >= ? AND orders.placed_on <= ?', d1, d2)

现在您可以在字段上聚合:

result = rel.select('categories.id, categories.name, SUM(order_items.qty) as qty, SUM(order_items.qty * order_items.price) as total')
  .group('categories.id, categories.name')
sample = result.first
sample.id # => 1
sample.name # => "Category 1"
sample.qty  # => 100.0
sample.total # => 500.0
0

精彩评论

暂无评论...
验证码 换一张
取 消