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:
Orderplaced_onOrderItemorder_id, product_id, amountProductfamily_idFamily
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.
3 Answers
Answers 1
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.
Answers 2
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 Answers 3
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
0 comments:
Post a Comment