Massaging sparse data from Rails' ActiveRecord into format friendly for HTML table?

Question

I'm looking for a best practice for what must be a common scenario: massaging sparse records pulled from a Rails (ActiveRecord, SQL) db table into a structure friendly for rendering tables in HTML.

For performance reasons I do a single query which returns data like this (I've simplified the example for clarity):

Lineitem.all
=> [#<Lineitem id: 1, username: "Bob", category: "A", amount: 10>, 
    #<Lineitem id: 2, username: "Bob", category: "C", amount: 20>, 
    #<Lineitem id: 3, username: "Jen", category: "A", amount: 30>, 
    #<Lineitem id: 4, username: "Ken", category: "B", amount: 40>,
    #<Lineitem id: 5, username: "Ken", category: "E", amount: 50>]

My goal is an HTML table like this:

             A     B     C     D     E
           ---   ---   ---   ---   ---
  Bob       10          20
  Jen       30   
  Ken             40                50
  Sam

This would be trivial if each category was stored as a separate column in the db (or if I was using NoSQL...?!) or if I didn't care about db performance.

To solve it I keep writing smelly helper code like this:

# create hash lookup, index first by username then by category, eg:
#   ["Bob"]["A"] = #<Lineitem id: 1, ...>
#   ["Bob"]["C"] = #<Lineitem id: 2, ...>
#   ["Jen"]["A"] = #<Lineitem id: 3, ...>  ...
def index_lineitems(lineitems)
  h = {}
  lineitems.each do |li|
    h[li.username] = {} unless h.key? li.username
    h[li.username][li.category] = li
  end
  h
end

# look up value from indexed hash
def get_lineitem_amount(indexed_lineitems, username, category)
  if indexed_lineitems.key?(username) && indexed_lineitems[username].key?(category)
    indexed_lineitems[username][category].amount
  else
    ""
  end
end

or some variation on this. Then I determine what the final list of rows and columns will be (note the "Sam" row...) and render the HTML table by looping and calling get_lineitem_amount each time. This is such bad code I'm embarrassed to share it.

Surely there is a cleaner, more OO and Rails-friendly approach to this common problem.

Any advice?

Answer 1

I'm doing something very similar slightly cleaner:

Let's say this is in the controller:

@data = LineItem.all

And this is the view

columns = @data.map(&:category).uniq

%table
  %thead
    %tr
      %th  
      - columns.each do |column|
        %th= column
  %tbody
    - @data.group_by(&:username).each do |username, rows|
      %tr
        %td= username
        - cursor = 0
        - rows.group_by(&:category).sort_by{|cat,rows| columns.index(cat)}.each do |category, rows|
          - until cursor == columns.index(category) do
            - cursor += 1
            %td  
          %td= rows.sum(&:amount)

It gets a ton cleaner if you store the columns in a separate DB table and just INCLUDE them into the current model so you can store index positions right on the object instead of needing to calculate them on the fly and not having good control of the order. One extra query isn't really going to break an app's performance.