1

My problem is two folded. First, I have a table that's not normalized that contains 3 pairs of data (6 columns), which have to be summed. It goes something roughly like this:

|-module_a-|-value_a-|-module_b-|-value_b-|-module_c-|-value_c-|
----------------------------------------------------------------
|   02     |    15   |    nil   |   nil   |   nil    |   nil   |
|   01     |    10   |    00    |   10    |   nil    |   nil   |
|   00     |    25   |    nil   |   nil   |   nil    |   nil   |
|   02     |    22   |    01    |   15    |   03     |   20    |
|   03     |    10   |    nil   |   nil   |   nil    |   nil   |
|   02     |     8   |    00    |   5     |   nil    |   nil   |

My main goal is to get a total sum of each module, but since they can be mixed in 3 fields, it becomes complicated to do it through activerecord (or so I think).

Anyway, to clean that up, I make an array like this:

tok1=Office.select('module_a as module, value_a as value')
tok2=Office.select('module_b as module, value_b as value').where.not(:value_b => nil)
tok3=Office.select('module_c as module, value_c as value').where.not(:value_c => nil)

tok=(tok1+tok2+tok3)

Result:

|-module-|-value-|
------------------
|   02   |  15   |
|   01   |  10   |
|   00   |  25   |
|   02   |  22   |
|   03   |  10   |
|   02   |   8   |
|   00   |  10   |
|   01   |  15   |
|   03   |  20   |
|   00   |   5   |

So far, so good.

Now, I'd like to make a sum of the module values. This would be very simple if I could do a GROUP and SUM via ActiveRecord, but the resulting variable tok is an array, so no ActiveRecord functions are possible. If I try to merge them through tok=tok1.merge(tok2) I end up with the results of tok2 only, which I assume is cause I'm renaming the fields when I query the Office table, I'm not sure. I tried putting them together in different ways, but I get an error that the relations are not immutable.

Anyway, since the result of doing tok=tok1+tok2+tok3 ends up with array, I'd like to do the sum by treating it as an array. I've tried using map, and after doing a tok.map{|h| [h.module, h.value]} I end up with an array like this:

{[02, 15], [01, 10], [00,  25}, [02, 22], [03,10], [02, 8], [00,10], [01,15], [03,20], [00, 5]}

I don't know how to proceed from here. I tried turning it into a hash, but I'm still too new to the map method so I don't know how to do that exactly. Ideally, I think, it should look like this:

{ ["02" => 15],["01" => 10],["00" => 25],["02" => 22],["03" => 10],["02" => 8],
  ["00" => 10],["01" => 15],["03" => 20],["00" => 5]}

I've read in other questions how to sum the values with a hash/array like that, so if I were to get it like this I think I'd be set.

Sorry for the messy explanation.

Improve this question
2
  • module is a reserved word in Ruby, so be careful about how you use it. You may get unexpected results or errors trying to access an attribute named module (such as used in your SELECT clauses above). Commented Oct 20, 2014 at 12:55
  • It's just an example. I don't actually have a field with that name. Commented Oct 20, 2014 at 12:56

2 Answers 2

Reset to default
4

You can do this with something like this:

arr = [[02, 15], [01, 10], [00,  25], [02, 22], [03,10], [02, 8], [00,10], [01,15], [03,20], [00, 5]]
hash = {}
arr.each do |item|
  hash[item[0]] ||= 0
  hash[item[0]] += item[1]
end
hash # => {2=>45, 1=>25, 0=>40, 3=>30}

or without previous map:

hash = {}
tok.each do |item|
  hash[item.module] ||= 0
  hash[item.module] += item.value
end

Edit: As mentioned in comment by Dima Goltsman (thanks!) it can be rewritten in even shorter form using inject:

arr = [[02, 15], [01, 10], [00,  25], [02, 22], [03,10], [02, 8], [00,10], [01,15], [03,20], [00, 5]]    
arr.inject(Hash.new(0)) do |hash, item|
  hash[item[0]] += item[1]
  hash
end # => {2=>45, 1=>25, 0=>40, 3=>30}

or

tok.inject(Hash.new(0)) do |hash, item|
  hash[item.module] += item.value
  hash
end
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Interesting. Could you explain to me what hash[item[0]] ||= 0 is doing?
It's actually a shortcut for: hash[item[0]] || hash[item[0]] = 0. Therefore if hash[item[0]] is already present - nothing will happen. If it's nil (or false) - it will be initialized with value 0. It's one of the most common Ruby idioms - you can find a lot of information about it here stackoverflow.com/questions/613985/common-ruby-idioms
better to use inject method, its a shortcut for what you did
2

Databases are normally really good at grouping and adding things, so I would probably do as much of this in SQL as possible.

tok = Office.find_by_sql <<-END_SQL
  SELECT module, SUM(value)
  FROM (
    SELECT module_a AS module, value_a AS value FROM offices
    UNION
    SELECT module_b AS module, value_b AS value FROM offices WHERE value_b IS NOT NULL
    UNION
    SELECT module_c AS module, value_c AS value FROM offices WHERE value_c IS NOT NULL
  ) AS modules(module, value)
  GROUP BY module
END_SQL

Evaluates to a collection of Office instances with the following attributes:

+--------+-------+
| module | value |
+--------+-------+
| 1      | 25    |
| 3      | 30    |
| 0      | 40    |
| 2      | 45    |
+--------+-------+

If it were up to me, I would likely create a view with the given SQL query and add an ActiveRecord data model on top of it. That way the Office model would not be used for purposes other than originally intended. Since implementing views varies between DBMS's and branches way off from the original topic I will not go into details here.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.