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).

can't you just call count on the AREL lookup? this would be performed in sql and should be performant — engineerDave, Dec 04 '18 at 17:32
I don't know. I have just used Arel because of some rails deprecation warning. — pzin, Dec 04 '18 at 17:41
sry my bad. I forget that arel has been merged into core. by arel I mean the ActiveRecord_Relation object, i.e. Order.date_range(from, to).count — engineerDave, Dec 04 '18 at 17:54
Perhaps this answer can help https://stackoverflow.com/a/2722426/793330 — engineerDave, Dec 04 '18 at 18:00
as a comment, why an item has many items? taking this aside, I think that you can ask for items directly and not for orders, it can simplify things, think in something like this `Item.joins(:orders).where(orders: {date: from..to}).group('items.product_id').sum('units')` tell me if it helps you and I will create the answer. — xploshioOn, Dec 10 '18 at 13:57
Thank you xploshioOn. Running that, throws this error `PG::GroupingError: ERROR: column "items.category" must appear in the GROUP BY clause or be used in an aggregate function`. Adding `.select("items.*")` does not solve it. Leaving out the grouping it does work, but I need the group. — pzin, Dec 10 '18 at 21:32
btw, the has many items thing was a copy failure. It's edited on the question. — pzin, Dec 10 '18 at 21:58
try letting the group part like this `...group(:product_id).sum...` — xploshioOn, Dec 11 '18 at 00:00
If I do so `Item.joins(:order).where(orders: {created_at: @from..@to}).group(:product_id).sum('units')` I get the same error. I do not know if it is related to https://stackoverflow.com/a/29661654/1706309. — pzin, Dec 11 '18 at 08:46
Try `Item.joins(:order).where(orders: {created_at: @from..@to}).group(:product_id).pluck('items.product_id, sum(items.units)')` or please provide desired result. — Pavel Mikhailyuk, Dec 11 '18 at 09:04
That gaves me the same error. So I added category and got the same error with code, and added it to and it worked: `Item.joins(:order).where(orders: {created_at: @from..@to}).group('product_id, category, code').pluck('items.product_id, sum(items.units)')`. — pzin, Dec 11 '18 at 11:08
awesome @pzin, if you are ok with it, I could create the answer to close this one. — xploshioOn, Dec 11 '18 at 17:33

Tom · Accepted Answer · 2018-12-13T04:39:30.690

4

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.

edited Dec 13 '18 at 04:39

answered Dec 13 '18 at 04:28

Tom

1,311
10
18

Both answers are working well. I will accept this one, because it feels more the rails way and it is faster (0.0897 vs 0.3538). Thank you. – pzin Dec 15 '18 at 09:45

score 2 · Answer 2 · answered Dec 11 '18 at 21:53

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)')

Group and count products on items from orders

2 Answers2