Group and count products on items from orders

Question

I have these three models:

class Order
  has_many :items
end

class Item
  belongs_to :order
  belongs_to :product
end

class Product
  has_many :orders, through: :items
  has_many :items
end

And I need to make a list of how many of each product are in the orders from a given date range.

I have a scope to get all orders in a date range:

Order.date_range(from, to)

Now I need to group and count; there are product_id and units fields on the item model.

I have seen solutions but just in cases with two tables (order>product) but not with three (order>item>product).

Answer 1

You can add the relation has_many :products, through: :items to the Order model.

Then Order.date_range(from, to).joins(:products).group(:product_id).count (You can also do group('products.id'))

If the items.units column isn't always 1 and needs to be summed across you can do: Order.date_range(from, to).joins(:products).group(:product_id).sum(:units)

The return will be a hash of {product1.id => count(product1.id), ...} or {product1.id => sum(units1), ...} respectively for each product in the order.

Answer 2

In this specific case, you can ask for items directly and not for orders, it can simplify things, working with just 2 and not 3 tables.

Item.joins(:order).where(orders: {created_at: @from..@to}).group('product_id, category, code').pluck('items.product_id, sum(items.units)')