CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
rico_mac
rico_mac

Reputation: 888

Aggregate values on an attribute returned by a has_many through

Data structure is as follows:

class Job 
  has_many job_sections 
  has_many job_products, through job_sections 
end

class JobSection 
  belongs_to job
  has_many job_products 
end

class JobProduct 
  belongs_to product
  belongs_to job_section 
end

When I call job.job_products i could end up with something like this:

 #<JobProduct:0x007ff4128b0ca0
  id: 18133,
  product_id: 250,
  quantity: 3,
  frozen_cache: {},
  discount: 0.0,
 #<JobProduct:0x007ff4128b00c0
  id: 18134,
  product_id: 250,
  quantity: 1,
  frozen_cache: {},
  discount: 0.0]

As you can see the product_id is identical in both instances. How do I merge the contents of the arrays by product id so I retrieve and act on them as aggregated values?

In a way, I need to be able to act on job products by their product_id rather than their id.

Effectively the result being something like this...

 [#<SomeFancySeerviceObjectMaybe?
  product_id: 250,
  quantity: 4,
  frozen_cache: {},
  discount: 0.0]

Do I opt for a little Plain Old Ruby Object to handle them all, or do I have to rethink the architecture of this, or is there (hopefully!) a bit of Rails secret sauce that than can help me out?

*FYI Job Section is a recent addition to the architecture, and I don't think its has been particularly well thought through. However, I can't spend too much time reversing what's already in place. This set up isn't ideal, I'm probably the sixth dev in as many years to start picking this apart.

Your suggestions are most welcome. Thank you

Upvotes: 0

Views: 126

Answers (1)

Paul A Jungwirth
Paul A Jungwirth

Reputation: 24541

In SQL this would be something like this:

SELECT SUM(quantity)
FROM   job_products
WHERE  product_id = 250
GROUP BY product_id

You can do that in ActiveRecord too. If you just want an integer, you can use pluck:

total_quantity = job.job_products.
  group(:product_id).
  pluck("SUM(job_products.quantity)").
  first

You can also pluck several columns if you want (in Rails 4+), which is why it returns an array. So if you want average discount at the same time, it's easy.

If you would prefer a JobProduct instance, you can get that too, but in your case a lot of the attributes will be nil because of the grouping. But you can say:

summary = job.job_products.
  group(:product_id).
  select("SUM(job_products.quantity) AS total_quantity").
  first

And you'll get a read-only JobProduct with an extra attribute named total_quantity. So you can do summary.total_quantity. But because of the grouping, summary will have a nil id, discount, etc. Basically it will only have attributes matching the things you select. This is a little weird, but sometimes it lets you write methods that work both on "real" JobProducts and for these summary versions.

Upvotes: 1

Related Questions