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.