Count sold products by specific families in orders by date

Question

I'm trying to figured out the best way to count sold products by a given date range on orders by a specific family.

These are my simplified models:

Order placed_on
OrderItem order_id, product_id, amount
Product family_id
Family

So, given now some dates, say d1 and d2, I need to count how many Product of a given Family are in those Order.

The desired output would be something like this:

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

I know how to do it looping all over the orders, but I think there should be a better way.

so u want product counts per order family? Like example order has product1 family 1 product2 family2 u get 2 counts with value 1 ? — Imre Raudsepp, Apr 20 '18 at 10:35
I would to count how many products from a certain family have been sold in a time range. It would produce something like product1 = 123, product2 = 345, etc. But, as said, products from a family (Product model as a family_id attribute). — pzin, Apr 20 '18 at 11:30

jpgbarbosa · Answer 1 · 2018-04-26T11:12:42.110

Assuming your data model and variables it should be something like:

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

This will generate the following 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"

and return the following structure:

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

Also I'm assuming you want to sum the amount of each product. Let me know if works for you.

score 1 · Answer 2 · answered Apr 20 '18 at 16:05

class CreateOrders < ActiveRecord::Migration[5.1]
  def change
    create_table :orders do |t|
      t.timestamps
    end
  end
end

class CreateOrderItems < ActiveRecord::Migration[5.1]
  def change
    create_table :order_items do |t|
      t.integer :order_id, index:true
      t.integer :product_id, index:true
      t.integer :amount
      t.timestamps
    end
  end
end

class CreateProducts < ActiveRecord::Migration[5.1]
  def change
    create_table :products do |t|
      t.text :name
      t.integer :family_id, index:true
      t.timestamps
    end
  end
end

class CreateFamilies < ActiveRecord::Migration[5.1]
  def change
    create_table :families do |t|
      t.text :name
      t.timestamps
    end
  end
end

class Family < ApplicationRecord
  has_many :products
end

class Order < ApplicationRecord
  has_many :order_items
  has_many :products, through: :order_items
end

class OrderItem < ApplicationRecord
  belongs_to :order
  belongs_to :product
end

class Product < ApplicationRecord
  belongs_to :family
  has_many :order_items
end

irb(main):015:0> Order.joins(:order_items).joins(:products).where("products.family_id":2).where("orders.created_at": [(Time.now).to_date..(Time.now + 1.day).to_date]).count
   (0.6ms)  SELECT COUNT(*) FROM "orders" INNER JOIN "order_items" ON "order_items"."order_id" = "orders"."id" INNER JOIN "order_items" "order_items_orders_join" ON "order_items_orders_join"."order_id" = "orders"."id" INNER JOIN "products" ON "products"."id" = "order_items_orders_join"."product_id" WHERE "products"."family_id" = ? AND ("orders"."created_at" BETWEEN '2018-04-20' AND '2018-04-21')  [["family_id", 2]]
=> 3

that's almost what I am looking for. Just that this counts every product with from the #2 Family, and I was looking for a group counting. Output something like [{"'product_2": 345}, {"product_34": 55}, {"product_3": 422}…]. — pzin, Apr 24 '18 at 08:32
If I add a `group` method to group by *order_items.product_id* then it also counts other products from different families which appear in the same orders where the desired family products are. — pzin, Apr 25 '18 at 07:41

score 1 · Answer 3 · answered May 01 '18 at 17:18

Not the full answer, as you have two pretty detailed ones already - but some notes

I'd setup scopes ... because we're not sure if how vertical your db is ... also, do avoid any time range issues, ensure you get the whole day before feeding the mess into the query. These are just examples not syntax checked or recommended best forms

  # ensure we are getting whole of each day
  full_d1 = d1.beginning_of_day
  full_d2 = d2.end_of_day

  #  change the order based on whatever you have more of
  scope :orders -> {where(created_at: full_d1..full_d2)}
  scope :family -> {where(product: family_id)}

  # use something like - swap order based on db conditions
  Order.orders.family  # ... then add the rest of what they are throwing down or .size / .count

Also, as noted elsewhere in S/O you can cache counts on has_many relationships - link

Count sold products by specific families in orders by date

3 Answers3