Using group_to() and aggregation for a simple inventory system @ a field hospital in Haiti

rubyonrails-talk
1

Hi, I'm pretty much a Rails newbie here...

I put together a very simple pharmaceutical inventory system for a field hospital in Haiti. When we first started, there was nothing but a list of medicines that were known to be on site. Now, a group started a robust inventory and I need to present the results differently.

I have the following tables and fields:

Model: Medicine Table: medicines Fields, id, name has_many :stocks

Model: Stock Table: stocks Fields: id, medicine_id, route_id, strength, amount_received, amount_dispensed belongs_to :medicine belongs_to :route

Model: Route Table: routes Fields: id, name has_many :stocks

The "stocks" table is used as a pharmacy log -- people check out medicines that they take for patients and check in medicines that arrive as donations. I currently have a method in the Medicine model that looks like this:

  def amount_on_hand     stocks.to_a.sum { |rec| (rec.amount_received - rec.amount_dispensed)}   end

On the page that lists all of the medicines, I call medicine.amount_on_hand to display a generic number of how many units of that type of medicine are on hand.

... Ibuprofen -- 500 Morphine -- 1000 etc....

There now are over 700 types of medicine on hand and many of them come in a variety of routes and strengths. Routes are: oral tablet, oral suspension, IV, intramuscular, etc.... Strengths are: 50mg, 20mg/dL, etc.... The strength field in the stocks table is a text field. There are so many different strengths that it wasn't feasible to standardize that at the outset.

Now, I need to group the stocks that relate to each medicine by Route and then try to perform aggregate sums based on the strength. In other words, it might look like:

Ibuprofen -- Oral Tablet -- 200 mg -- 1000 -- Oral Tablet -- 500 mg -- 200 -- Oral Suspension -- 50 mg/dL -- 45

Morphine -- Inject -- 2mg/dL -- 450 -- Inject -- 4mg/dL -- 800

etc...

I've been reading on the group_by() method for enumeration, but I don't know how to take the results returned (ordered hash, right?) and perform the summary methods on them. My guess is that I need to write another method in the Medicine model that iterates through the 'stocks' table to generate the results for each different medicine type -- but I'm having trouble with this.

Any help with this method or suggestions for alternative approaches would be appreciated! I'd like to get the system updated as soon as I can. Thanks!

2

You might get a quicker result by doing a query direct to the DB and using its GROUP BY functionality:

SELECT sum( s.amount_received ) - sum(amount_dispensed) as amount_on_hand, s.strength, r.name AS route, m.name AS medicine FROM stocks s JOIN medicines m ON s.medicine_id = m.id JOIN routes r ON s.route_id = r.id GROUP BY s.strength, r.name, m.name

You can use this in a "find_by_sql" and iterate it as a normal array....

Not exactly Rails-y. but let's face it, we need to know what drugs are in the cupboard right now.

3

Using Enumerable.group_by:

Stock.all.group_by{|s| [s.medicine.name, s.route.name, s.strength]}.each do |s|   puts "#{s.first.inspect} #{s.last.sum(&:amount_received) - s.last.sum(&:amount_dispensed)}" end

Does that give you enough to work with?

4

I think so. I can create a method in the Medicine model that includes this code and then call that method on each med on the the show and/or index views, right?

If I go with the SQL you suggested above, where would I use that? Also in a method in the Model?

5

Using Enumerable.group_by:

Stock.all.group_by{|s| [s.medicine.name, s.route.name, s.strength]}.each do |s| puts "#{s.first.inspect} #{s.last.sum(&:amount_received) - s.last.sum(&:amount_dispensed)}" end

Does that give you enough to work with?

I think so. I can create a method in the Medicine model that includes this code and then call that method on each med on the the show and/or index views, right?

In the Medicine model it might work like this: def grouped_stocks   stocks.group_by{|s| [s.medicine.name, s.route.name, s.strength]} end

then in the view you can play with the iteration:

<% @medicine.grouped_stocks.each do |s| %>   <%= "#{s.first.inspect} #{s.last.sum(&:amount_received) - s.last.sum(&:amount_dispensed)}" %> <% end %> (... totally untested I'm afraid)

If I go with the SQL you suggested above, where would I use that? Also in a method in the Model?

That would more likely be better used to populate a variable in the controller that's passed to the view... it's not a very nice method of getting data in Rails (ignoring the DB abstraction!), but it's easy to tweak (add WHERE clauses to restrict by medicine, etc) and iterate through for quick results.

Play with them both in an IRB console (and your DB management tool) - that's what I did to get close to what I thought might do the job.

And post again if you need any more help.

6

Hey, thanks for the help so far. I went with a method in the Medicine.rb model file and I'm trying to display the results through the view, using code similar to what you suggested. I modified the code to appear as follows for now:

def grouped_stocks   stocks.group_by{|s| [s.route.name, s.strength]} end

The result is an ordered hash, correct? Here's some really ugly code in the view: <%=h "#{s.first}" %> This prints to screen as a concatenation of the two values in they key of the hash item, like:

Oral Suspension100mg/5mL

There's no space between the two parts of the key.

So I tried this:

<%=h "#{s.first.first}" %>

This returns just the route name, which is good for display: Oral Suspension

To return just the strength, I put this in place

<%=h "#{s.first.last}" %>

Which returns: 100mg/5mL

This strikes me as the wrong way to access these parts of the hash. Is there a better way to do this?

What is the syntax for extracting a single component of the value part of this hash? Let's say that I wanted to extract the strength from the value part of the hash instead of the key part of the hash (since it occurs in both).

I tried the following:

<%=h "#{s.last(:strength)}" %> <%=h "#{s.last.first}" %>

and a few more, but since the value of the hash is an object, I always see something like: #<Stock:0x1032938e0>

What's the proper way to access attributes of the object that is returned as the value of the hash?

Also, is it possible to order the hash so that they appear in descending order of how much of each item is in stock? In other words, sort descending by the value that the following generates?

<%=h "#{s.last.sum(&:amount_received) - s.last.sum(&:amount_dispensed)}" %>

7

And post again if you need any more help.

Hey, thanks for the help so far. I went with a method in the Medicine.rb model file and I'm trying to display the results through the view, using code similar to what you suggested. I modified the code to appear as follows for now:

No worries (although if you're *not* working for a charity in Haiti there's gonna be hell to pay!)

def grouped_stocks stocks.group_by{|s| [s.route.name, s.strength]} end

The result is an ordered hash, correct? Here's some really ugly code in the view:

<snipped extremely ugly code>

You *can* do it by chopping up the key, but essentially you could't care less about the key - it's just a method of grouping all the items together how we want.

If you iterate down to the individual stocks arrayed inside the hash, you should have access to everything you need from there:

<% @medicine.grouped_stocks.each_pair do |k, v|   v.each do |stock|     %>     <%= "#{stock.medicine.name} #{stock.strength} #{stock.route.name}" %>     <%   end end %>

Also, is it possible to order the hash so that they appear in descending order of how much of each item is in stock? In other words, sort descending by the value that the following generates?

<%=h "#{s.last.sum(&:amount_received) - s.last.sum(&:amount_dispensed)}" %>

Good god man! :slight_smile: erm... off the top of my head I don't know (as I hadn't played with group_by until last night). Whether the .sort method works on the OrderedHash (the API is eluding me at the moment), or whether you'd need to extract the keys and totals to an array of arrays and sort that by totals (so then rather than iterating grouped_stocks, you access it by key taken from the sorted array), I don't know which...

Re-reading it: Does that make any sense?!

8

how about:

<% @medicine.grouped_stocks.each_pair do |k, v| v.sort_by {|stock| stock.amount_on_hand}.each do |stock|    %>    <%= "#{stock.medicine.name} #{stock.strength} #{stock.route.name} #{stock.amount_on_hand}" %>    <% end end %>

not sure if that will sort ascending or descending though... if it's the wrong way round, you can always reverse before you .each...

9

That makes sense -- the only issue is that amount_on_hand is a method in the Medicine model, not in the Stock model -- it performs an aggregate sum for all of the stocks belonging to the medicine in question. This code uses it as a Stock method, doesn't it?

I can add a version of it to the Stock model. How would that look? This is the version in the Medicine model:

def amount_on_hand     stocks.to_a.sum { |rec| (rec.amount_received - rec.amount_dispensed)} end

10

the only issue is that amount_on_hand is a method in the Medicine model, not in the Stock model -- it performs an aggregate sum for all of the stocks belonging to the medicine in question. This code uses it as a Stock method, doesn't it?

Of course - so that's a pain...

Well, there's always the approach of extracting the keys, sorting them, and then accessing the grouped_stocks by key:

# Medicine model   def grouped_stocks_keys_ordered_by_amount_on_hand     grouped_stocks.map {|gs| [gs.first, (gs.last.sum(&:amount_received) - gs.last.sum(&:amount_dispensed))]}.sort_by {|grouping| grouping[1]}.map {|key_pair| key_pair[0]}   end

#IRB

Medicine.first.grouped_stocks_keys_ordered_by_amount_on_hand.each do |key|

?> puts "STOCK FOR #{key.inspect}"

puts m.grouped_stocks[key].inspect end

This should loop through the keys and pull out the grouped_stocks value for that key. To save building the hash of grouped_stocks for every iteration through the keys, you can memoize it:

  def grouped_stocks     @grouped_stocks ||= stocks.group_by{|s| [s.route.name, s.strength]}   end

I hope you can see that you can alter the IRB example to use in your view to draw a table (or whatever) for the data in the order you want.

The "grouped_stocks_keys..." method can probably be made more efficient (and certainly more legible!) with some refactoring (a few applications of Extract Method would make it clearer), but I like to get stuff working first and streamline after.