CODEWITHSUNDEEP
ruby-on-railsactiverecord
pzin
pzin

Reputation: 4248

Count sold products by specific families in orders by date

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:

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.

Upvotes: 3

Views: 180

Answers (3)

Mirv - Matt
Mirv - Matt

Reputation: 554

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

Upvotes: 1

jpgbarbosa
jpgbarbosa

Reputation: 780

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.

Upvotes: 3

Imre Raudsepp
Imre Raudsepp

Reputation: 1198

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

Upvotes: 1

Related Questions