Displaying Usage Statistics on a Bar Graph

My last post was about creating images of line graphs in Rails. Now I am going to have a go at histograms (bar graphs), using HTML to draw the graph, rather than creating an image.

The Data
I recently wanted to see how frequently entries were added to a table in my database. Each entry has a created_at column, so it should be easy to break this down by week or by month. Or so I thought. It was not as easy as I had hoped. There is no simple Rails way to group records within a set time interval. Instead, I had to use some actual SQL. And as my development PC is running MySQL and my server has PostgreSQL, I had to use two different dialects of SQL. It is not pretty, but this is what worked for me. First, I set up a SQL string (I did this in the controller while trying it out, but moved it to config/initializers/constants.rb later; you need to restart the web server after changing constants.rb):

module Sql
# SQL to get usage stats by week

# Different SQL for different dialects
# Both will generate the number of days since the year dot
# when the records was created
# MySQL has year and dayofyear functions
MYSQL_YEAR = 'year(created_at)'
MYSQL_DAY = 'dayofyear(created_at)'
# PostgreSQL uses the extract function for both
POSTGRESQL_YEAR = 'extract(year from created_at)'
POSTGRESQL_DAY = 'extract(doy from created_at)'
# Select the correct SQL based on the name of the database adapter
SQL_YEAR = ActiveRecord::Base.configurations[RAILS_ENV]['adapter'].match(/mysql/) ?
             MYSQL_YEAR : POSTGRESQL_YEAR
SQL_DAY = ActiveRecord::Base.configurations[RAILS_ENV]['adapter'].match(/mysql/) ?
             MYSQL_DAY : POSTGRESQL_DAY

# The rest of the SQL is the same
# The second line calculates the week number for the record
SQL =
<<END
select count(sample_reference) as count,
      floor((#{SQL_YEAR} * 365 + #{SQL_DAY}) / 7) as week,
      #{SQL_YEAR} as year,
      floor(#{SQL_DAY} / 7) as week_no
      from samples
      group by week
      order by week desc
      ;
END

end

You do have to careful when invoking SQL directly that you guard against SQL injection attacks. Not a problem in this case as the SQL command is in no way altered by the end-user.

SQL does have functions for getting the week number, however, funny things happen just before the new year, with records created in week zero, but in the previous year, so I abandoned using these functions. Months would have easier, but I wanted weeks.

The Controller
After all that, the method in my controller is fairly simple. Some sites I looked at used establish_connection before connection. As I understand it, establish_connection tells Rails that you want to potentially make a connection to a database, allowing you to create actual connections as oftyen as you like in the future. Rails does that automatically for ActiveRecord, so there is no need in this situation for me to. The map method adds a new key-value pair to each hash in the array; this will be used for the labels (eg "2009 week 3").

def graph
ary = ActiveRecord::Base.connection.select_all(Sql::SQL)
@result = ary.map do |e|
  e['label'] = "#{e['year']} week #{e['week_no']}"
  e
end
end

A Generic Partial
I created a partial to display the results in the bar graph. This is a general thing that could be used in any number of web pages to display this sort of data. It uses HTML to extend a small image across a table, according to the data. Note that this uses the find_best method I described on the blocks page; this pulls out the entry with the highest count, against which all the others are scaled.

<table>
<%
max = data.find_best { |x, y| x['count'].to_i < y['count'].to_i }['count'].to_i
data.each do |element|
  count = element['count'].to_i
  size = "#{count * 800 / max}x20"
%>
<tr>
  <td><%=h element['label'] %></td>
  <td><%=h count %></td>
  <td><%= image_tag 'bar.gif', :size => size %></td>
</tr>
<% end %>
</table>

The View
The partial is invoked like this:

<%= render :partial => 'bargraph',
              :locals => { :data => @result } %>

In the locals hash, :data is mapped to the array that holds the data. Each entry in the array must have a 'count' value, used for the size of the bar, and another value, the key for which is 'label'.

Struggling with Ruby: Contents Page