Recently I needed to make a custom view for activity logs in my Ruby on Rails app. I wanted to create a summary of the activity grouped by the day, without any duplicates. e.g. if I performed an action an object twice, I didn’t want it to be listed twice.
So how does one do this in Rails? Simple – use the :group (i.e. GROUP BY) parameter when doing a find() to combine results into single results.
The nitty gritty
For reference I used the following schema on my logs:
create_table "application_logs" do |t|
t.integer "rel_object_id"
t.text "object_name"
t.string "rel_object_type"
t.datetime "created_on",
t.integer "created_by_id",
t.boolean "is_private",
t.boolean "is_silent",
t.integer "action_id",
t.integer "page_id",
endIn my case, most of my objects were linked to pages. And for those, I only wanted page activity to be listed once. e.g. “Modified page X” instead of “Modified object 1 on page X”, “Modified object 2 on page X”.
So I needed to group by page_id, created_by_id, created_on (as a date), and both rel_object_id and rel_object_id.
First I came up with the following :group :
"created_by_id,
date(created_on),
page_id,
rel_object_type || rel_object_id"Note: the “||” operator in SQLite and PostgreSQL concatenates strings.
Unfortunately that doesn’t work properly since I have other objects which aren’t linked to a page, and they would only get listed once (since for all of them page_id would be NULL). So I needed to use the “CASE” statement to differentiate between the two:
"created_by_id,
date(created_on),
CASE page_id ISNULL
WHEN 1 THEN rel_object_type || rel_object_id
ELSE page_id
END"So now both the page objects and the regular objects were listed once per day. But there was another problem.
After the query I used the group_by method to group everything into blocks based on the date. But I also use the time zone support in Rails 2.1, and since the database stores its dates in UTC, I got this rather odd issue where in certain circumstances objects were listed twice.
In reality they were the same date, in UTC. But not in the current timezone I was using. So after a bit of investigation I came up with a solution. I needed to offset the date in the query by the UTC offset for the current timezone.
It turns out that there are a ton of different ways to do it, depending on which database you are using. In my case I was testing with SQLite, so the following sufficed:
date(created_on, '+#{Time.zone.utc_offset} seconds')And for MYSQL (and perhaps others), using INTERVAL works:
date(created_on + INTERVAL #{Time.zone.utc_offset} SECOND)Of course, this still has its problems. Like what about daylight savings time?
Unfortunately, since there doesn’t seem to be any set standard for specifying what timezone to evaluate times in, you are either going to have to write a specific case for it, or just put up with the dates potentially being off for an hour or two.
The end result
Well, it looks something like this:
