How to make a “pivot table” on Ruby on Rails?

Question

First, I'd like to mention I'm COMPLETELY new to Ruby and Rails, I'm on my very first days of learning, so I apologize if I seem a bit unclear or too broad with my questions.

I'm trying to do something simple (I think?), which is to pivot a table.

I have a table that looks like this:

----------------------------------
| Name     | Product ID | Amount |
|----------|----------------------
| Robert   |     P1     |   2    |
| Michael  |     P2     |   1    |
| Leonard  |     P2     |   1    |
| Robert   |     P2     |   4    |
| Robert   |     P3     |   2    |
| Michael  |     P3     |   1    |
----------------------------------

... and I'd like to to turn it into something like this:

---------------------------
| Name     | P1 | P2 | P3 |
---------------------------
| Robert   | 2  | 4  | 2  |
| Michael  | -  | 1  | 1  |
| Leonard  | -  | 1  | -  |
---------------------------

I'm not too sure how to achieve that. I've looked around and haven't found anything specific to my question.

I found a gem called pivot_table, which can be found here: https://github.com/edjames/pivot_table but I have no clue how to exactly use it. It has a small guide in it, but I don't know where to place the code.

Any help is greatly appreciated.

Thank you.

Answer 1

First install the gem

# In your Gemfile
gem 'pivot_table'

Then in your terminal, run

bundle install

Say the model represented by your first table is Sale.

sales = Sale.all
grid = PivotTable::Grid.new do |g|
  g.source_data  = sales
  g.column_name  = :product_id
  g.row_name     = :name
end

Then you can use the other methods listed in the docs. For example

g.column_headers # ['P1', 'P2', 'P3']

Note: this is just from reading the GitHub page you linked. I've never used the gem.

Edit:

You can put the code in a module:

# lib/PivotTable
module PivotTable
  def grid(data, options = {})
    grid = PivotTable::Grid.new do |g|
      g.source_data = data
      g.column_name = options[:column_name]
      g.row_name = options[:row_name]
    end
  end
end

Then you'd call it from somewhere else with

include PivotTable

def my_method
  sales = Sale.all
  grid = grid(sales, { :row_name => :name, :column_name => :product_id })
  # do stuff
end

This way you can reuse the grid-generating code and call it with arbitrary parameters.

Answer 2

Looking at your table and the results you're looking for, I would do it like this ( I assume it's an orders table ?)

result = []
Order.all.group_by(&:name).each do |name, orders|
  record = {}
  record["name"] = name
  orders.each do |order|
    record[order.product_id] = order.amount
  end
  result.append(record)
end 

I hope this will give you a good starting point !

Answer 3

This is a general solution, that I implemented in application_helper.rb

def pivot_table row_model, column_model, pivot_model, pivot_attribute
  row_model.all.map do |r|
    column_model.select(:id).sort.map do |c|
      pivot_model.find_by(
        row_model.to_s.downcase => r,
        column_model.to_s.downcase => c
      )&.public_send(pivot_attribute) || 0
    end
  end
end

The first three parameters are capital-letter ActiveRecord subclass names, and the pivot_attribute (which is an attribute of pivot_model) can be given as a symbol or a string. This assumes pivot_model is a many-to-many relation that references row_model and column_model as foreign keys. If there isn't a many-many model, I'm guessing some models could be repeated among the parameters, but I haven't kitchen tested it for that.

The return value is an array of arrays.