Rails count and grouping into columns for 7 days of the week

Question

Rails getting started with my first proper Rails app.

I have 2 tables

transactions

t.integer  "product_id"
t.text     "url"
t.text     "saletime"

products

t.text     "name"
t.decimal  "price"

Sample Data

transactions

product_id | url | saletime

  2     foo.com      2016-02-29T04:32:20+11:00
  2     foo.com      2016-02-26T04:32:20+11:00
  5     foo.com      2016-02-27T04:32:20+11:00
  1     foo.com      2016-02-25T04:32:20+11:00
  4     foo.com      2016-02-21T03:34:20+11:00
  3     foo.com      2016-02-29T02:24:20+11:00
  2     foo.com      2016-02-29T01:13:20+11:00

products

id | name | price

  1     Prod1      39.99
  2     Prod2      99.99
  3     Prod3      32.99
  4     Prod4      21.99
  5     Prod5      19.99

I would like to produce a view in Rails as follows (for the current week):

Product    Mon   Tues   Wed   Thurs   Fri    Sat   Sun    Total
Prod1      1     0      4     4       3      8     10
Prod2      0     2      5     1       4      3     0
Prod3      1     10     12    5       3      18    12
Prod4      1     0      2     4       3      0     10
Prod5      2     0      2     1       32     8     10

I'm struggling as to how to go about doing this. Should I try and write a complex SQL query to return this data directly or has anyone any pointers on how they would do this in Rails.

Thanks.

Answer 1

I'd start with the simplest possible solution. Depending on how often you check this page and on how many products and transactions you have this might be already good enough. So what is the simplest solution? I'd extract the logic into a Dashboard (or whatever you want to call it) object. Like this you don't clutter the models with code specific to the dashboard:

class Dashboard
  def initialize(range_start, range_end)
    @range_start = range_start
    @range_end = range_end
  end

  attr_reader :range_start, :range_end

  def range
    (range_start..range_end)
  end

  def products
    Product.joins(:transactions).where('date(transactions.recorded_at) >= :start AND date(transactions.recorded_at) <= :end', start: range_start, end: range_end).select("products.*").distinct
  end

  def transactions_on(product, date)
    product.transactions.where('date(recorded_at) = ?', date)
  end

  def transactions_in_range(product)
    product.transactions.where('date(transactions.recorded_at) >= :start AND date(transactions.recorded_at) <= :end', start: range_start, end: range_end)
  end
end

Then you can use this dashboard class in your view to build the table:

Displaying <%= @dashboard.range_start %> - <%= @dashboard.range_end %>
<table border="1">
  <thead>
    <tr>
      <th>Product</th>
      <% @dashboard.range.each do |date| %>
        <td><%= date.strftime('%^a') %></td>
      <% end %>
      <th>TOTAL</th>
    </tr>
  </thead>
  <tbody>
    <% @dashboard.products.each do |product| %>
      <tr>
        <td><%= product.name %></td>
        <% @dashboard.range.each do |date| %>
          <td><%= @dashboard.transactions_on(product, date).count %></td>
        <% end %>
        <td><%= @dashboard.transactions_in_range(product).count %></td>
      </tr>
    <% end %>
  </tbody>
</table>

This is pretty dynamic, you can change the range and the table will adapt. You can add pagination to display prev/next week or a bigger range.

In your dashboards controller:

  def show
    range_start = Time.zone.today.beginning_of_week - 1.week # would probably get it from params
    range_end = range_start.end_of_week
    @dashboard = Dashboard.new(range_start, range_end)
  end

This will trigger quite some count queries (1 + range.size * products.size). But the solution is easy to understand and maintain. If this works then i'd not spend much time to implement something overly clever.

One way to be clever is to use the fact that AR will add attributes automatically. You can add calculated fields using select and alias them. Afterwards you can use normal ruby to access the values. So if you want to retrieve all the values in a single SQL statement:

  def table
    scope = Product.all.select('products.*')
    range.each_with_index do |date, index|
      scope = scope.select("(SELECT count(*) from transactions t WHERE t.product_id = products.id AND date(t.recorded_at) = date('#{date.to_s(:db)}')) as d_#{index}")
    end
    scope = scope.select("(SELECT count(*) from transactions t WHERE t.product_id = products.id AND date(t.recorded_at) >= date('#{range_start.to_s(:db)}') AND date(recorded_at) <= date('#{range_end.to_s(:db)}')) as d_total")
    scope
  end

And then use this in your view:

<table border="1">
  <thead>
    <tr>
      <th>Product</th>
      <% @dashboard.range.each do |date| %>
        <td><%= date.strftime('%^a') %></td>
      <% end %>
      <th>TOTAL</th>
    </tr>
  </thead>
  <tbody>
    <%- @dashboard.table.each do |product| %>
      <tr>
        <td><%= product.name %></td>
        <% @dashboard.range.each_with_index do |date, index| %>
          <td><%= product.public_send("d_#{index}") %></td>
        <% end %>
        <td><%= product.public_send('d_total') %></td>
      </tr>
    <% end %>
  </tbody>
</table>

Needs some cleaning up. But it works. Note that the second solution currently displays ALL products. If you want to restrict to those that have a corresponding transaction, then you'd need to tweak it to your needs.