What is the best way to track product sales in the app and choose the best sales?

Question

I installed my databases as follows:

Product:

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

Order Items:

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

Order:

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

Now this setting is very difficult to choose best selling products in each week from each category. How can i fix this? Another sales tracking database? Please, help.

Answer 1

Basically you need to join tables categories, products, order_itemsand orders.

The connection can be made using the following code:

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"

Based on this, you can filter the date range.

, 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

Answer 2

. .

• date
• product_id
• qty
• , .

. , .

SELECT P.id, P.name, P.category_id, SUM(qty) as LastWeekSales
FROM Product as P INNER JOIN Order Items as OI 
                    ON P.id = OI.product_id
     INNER JOIN Order as O
                    ON O.id = OI.order_id
WHERE O.placed_on <= GetTodaysDate() AND O.placed_on > GetOneWeekBacksDate()
GROUPBY P.category_id
ORDERBY WeekSales

, , - ruby-on-rails. , .

Answer 3

+1, . - , . , , .

:

def self.last_week
  Order.where(" created_at >= ? ", 1.week.ago.utc)
end

:

@qty_hash = category = Hash.new 0; 
@output_hash = Hash.new { |hash, key| hash[key] = {} }

@recently_ordered_items = OrderItem.find_all_by_order_id(Order.last_week)
@recently_ordered_items.each { |i| @qty_hash[i.product_id] += i.qty }
@recent_products=Product.find_all_by_id(@qty_hash.keys)

@qty_hash.each do |key, value|
  @recent_products.each { |i| category = i.category_id if i.id == key }
  @output_hash[category] = @output_hash[category].merge(@qty_hash.slice(key))
end

@output_hash is the output and is in the format: {1 => {3 => 9}, 2 => {4 => 8, 6 => 5, 7 => 4}}

In this case, categories 1 and 2, product identifiers 3 (9 sold), 4 (8 sold), 6 (5 sold) and 7 (4 sold)

Tested and working. Good luck.