- Originally created by Cary Dunn
- Modified functionality and additional features by HouseKeeper
- Added the ability to sum a column – giving you a total ammount from a date until now or between 2 dates
- Added Between functionality so you can get activity or sums between 2 specified dates
- Created a gem version of this plugin
A simple way to grab recent activity or a sum of values on a given model from a table grouped by day, hour,
or week with only 1 SQL query and giving the ability to pad the results for days/weeks/hours with no activity.
The plugin offers the ability to…
- Present as a Google Chart (line or bar)
- Scope your model to given :conditions
- Pad your results for days with no activity
- Present your data from a set date until present or between two dates
NOTE: MySQL only because of date functions use.
Example Google Chart Generated&
Run the following command in your projects root
ruby script/plugin install git://github.com/housekeeper/has_activity.git
Run the following commands
gem sources -a http://gems.github.com (you only have to do this once)
sudo gem install housekeeper-has_activity
Add the following line inside Rails::Initializer.run do
config.gem 'housekeeper-has_activity', :lib => 'has_activity',
:source => 'http://gems.github.com'
class Feed < ActiveRecord::Base
# Defaults to created_at
has_activity
...
end
class Feed < ActiveRecord::Base
# Change the timestamp DB field to something custom
has_activity :by => "published_at"
...
end
# Get all activity since 1 week ago grouped by day (default)
Feed.activity_since(1.week.ago)
=> [{:activity=>0, :offset=>7, :created_at=>Mon Mar 09 10:27:34 -0700 2009}, {:activity=>0, :offset=>6, :created_at=>Tue Mar 10 10:27:34 -0700 2009}, {:activity=>8, :offset=>5, :created_at=>Wed Mar 11 01:19:09 -0700 2009}, {:activity=>2, :offset=>4, :created_at=>Thu Mar 12 04:26:09 -0700 2009}, {:activity=>0, :offset=>3, :created_at=>Fri Mar 13 10:27:34 -0700 2009}, {:activity=>0, :offset=>2, :created_at=>Sat Mar 14 10:27:34 -0700 2009}, {:activity=>0, :offset=>1, :created_at=>Sun Mar 15 10:27:34 -0700 2009}, {:activity=>0, :offset=>0, :created_at=>Mon Mar 16 10:27:34 -0700 2009}]
SQL generated for previous statement…
SELECT
published_at AS timestamp,
COUNT(*) AS activity_count,
DATEDIFF(now(), published_at) as days_ago
FROM feeds
WHERE 1=1 AND published_at > '2009-03-09 17:27:33'
GROUP BY days_ago
ORDER BY published_at ASC
# Get all activity since 1 day ago grouped by hour scoped to a certain user
Feed.activity_since(1.week.ago, :conditions => ["user_id = ?", current_user.id], :group_by => :hour)
=> [{:activity=>0, :offset=>24, :created_at=>Sun Mar 15 10:29:47 -0700 2009}, {:activity=>0, :offset=>23, :created_at=>Sun Mar 15 11:29:47 -0700 2009}, {:activity=>0, :offset=>22, :created_at=>Sun Mar 15 12:29:47 -0700 2009}, {:activity=>0, :offset=>21, :created_at=>Sun Mar 15 13:29:47 -0700 2009}, {:activity=>0, :offset=>20, :created_at=>Sun Mar 15 14:29:47 -0700 2009}, {:activity=>0, :offset=>19, :created_at=>Sun Mar 15 15:29:47 -0700 2009}, {:activity=>0, :offset=>18, :created_at=>Sun Mar 15 16:29:47 -0700 2009}, {:activity=>0, :offset=>17, :created_at=>Sun Mar 15 17:29:47 -0700 2009}, {:activity=>0, :offset=>16, :created_at=>Sun Mar 15 18:29:47 -0700 2009}, {:activity=>0, :offset=>15, :created_at=>Sun Mar 15 19:29:47 -0700 2009}, {:activity=>0, :offset=>14, :created_at=>Sun Mar 15 20:29:47 -0700 2009}, {:activity=>0, :offset=>13, :created_at=>Sun Mar 15 21:29:47 -0700 2009}, {:activity=>0, :offset=>12, :created_at=>Sun Mar 15 22:29:47 -0700 2009}, {:activity=>0, :offset=>11, :created_at=>Sun Mar 15 23:29:47 -0700 2009}, {:activity=>0, :offset=>10, :created_at=>Mon Mar 16 00:29:47 -0700 2009}, {:activity=>0, :offset=>9, :created_at=>Mon Mar 16 01:29:47 -0700 2009}, {:activity=>0, :offset=>8, :created_at=>Mon Mar 16 02:29:47 -0700 2009}, {:activity=>0, :offset=>7, :created_at=>Mon Mar 16 03:29:47 -0700 2009}, {:activity=>0, :offset=>6, :created_at=>Mon Mar 16 04:29:47 -0700 2009}, {:activity=>0, :offset=>5, :created_at=>Mon Mar 16 05:29:47 -0700 2009}, {:activity=>0, :offset=>4, :created_at=>Mon Mar 16 06:29:47 -0700 2009}, {:activity=>0, :offset=>3, :created_at=>Mon Mar 16 07:29:47 -0700 2009}, {:activity=>0, :offset=>2, :created_at=>Mon Mar 16 08:29:47 -0700 2009}, {:activity=>0, :offset=>1, :created_at=>Mon Mar 16 09:29:47 -0700 2009}, {:activity=>0, :offset=>0, :created_at=>Mon Mar 16 10:29:47 -0700 2009}]\
SQL generated for previous statement…
SELECT
published_at AS timestamp,
COUNT(*) AS activity_count,
((((YEAR(now()) - YEAR(published_at))*365)+(DAYOFYEAR(now())-DAYOFYEAR(published_at)))*24)+(HOUR(now())-HOUR(published_at)) as hours_ago,
CONCAT(YEAR(published_at), CONCAT(DAYOFYEAR(published_at), HOUR(published_at))) AS unique_hour
FROM feeds
WHERE user_id = 1 AND published_at > '2009-03-15 17:29:47'
GROUP BY unique_hour
ORDER BY published_at ASC
# Get all activity since 2 months ago grouped by week and order it from current week to oldest week
Feed.activity_since(2.months.ago, :group_by => :week, :order => :desc)
=> [{:activity=>0, :offset=>0, :created_at=>Mon Mar 16 10:34:28 -0700 2009}, {:activity=>0, :offset=>1, :created_at=>Mon Mar 09 10:34:28 -0700 2009}, {:activity=>0, :offset=>2, :created_at=>Mon Mar 02 10:34:28 -0800 2009}, {:activity=>0, :offset=>3, :created_at=>Mon Feb 23 10:34:28 -0800 2009}, {:activity=>0, :offset=>4, :created_at=>Mon Feb 16 10:34:28 -0800 2009}, {:activity=>0, :offset=>5, :created_at=>Mon Feb 09 10:34:28 -0800 2009}, {:activity=>0, :offset=>6, :created_at=>Mon Feb 02 10:34:28 -0800 2009}, {:activity=>0, :offset=>7, :created_at=>Mon Jan 26 10:34:28 -0800 2009}, {:activity=>0, :offset=>8, :created_at=>Mon Jan 19 10:34:28 -0800 2009}]
SQL generated for previous statement…
SELECT
published_at AS timestamp,
COUNT(*) AS activity_count,
((YEAR(now()) - YEAR(published_at))*52)+(WEEK(now())-WEEK(published_at)) as weeks_ago,
YEARWEEK(published_at) AS unique_week
FROM feeds
WHERE 1=1 AND published_at > '2009-01-16 17:34:28'
GROUP BY unique_week
ORDER BY published_at ASC
# Get Usd Net Commission in July ago grouped by day and order it in ascending order by the days commission was earnt on for account_id 1
AccountPnl.activity_between(july_month.beginning_of_month,
july_month.end_of_month,
:group_by => :day,
:padding => false,
:sum_on => "usd_net_commission",
:conditions => ["crm_account_id=?", 1],
:order => :asc)
SQL generated for previous statement…
SELECT
earnt_on AS timestamp,
COUNT(*) AS activity_count, SUM(usd_net_commission) as sum,
DATEDIFF('2009-07-31 23:59:59', earnt_on) as days_ago
FROM crm_daily_account_pnls
WHERE crm_account_id=16 AND earnt_on
BETWEEN '2009-07-01 00:00:00' AND '2009-07-31 23:59:59'
GROUP BY days_ago
ORDER BY earnt_on ASC
Feed.activity_since(6.months.ago, :group_by => :week, :order => :desc).to_activity_gchart
=> "http://chart.apis.google.com/chart?cht=bvs&chs=200x50&chd=t:0,10,15,3,8,4,0,3,2,3,0,11,4,23,14,0,0,0,13,0,4,8,19,10,12,21,1&chco=336699&chbh=a,2&chds=0,23&chf=bg,s,EFEFEF&"
Feed.activity_since(6.months.ago, :group_by => :week, :order => :desc).to_activity_gchart
AccountPnl.activity_between(july_month.beginning_of_month,
july_month.end_of_month,
:group_by => :day,
:padding => false,
:sum_on => "usd_net_commission",
:conditions => ["crm_account_id=?", 1],
:order => :asc).to_activity_gchart( :type => :line,
:column => :sum,
:size => "100x45",
:bgcolor => "f0ffdc",
:chart_color => "D1EAFF",
:area_color => "DFEBFF",
:line_color => "c7c7c7",
:line_width => "4"))
=> "http://chart.apis.google.com/chart?chs=100x45&cht=ls&chco=c7c7c7&chm=B,DFEBFF,0,0,0&chd=t:<data>&chds=0,0&chf=bg,s,f0ffdc&"
# Grabs a hash of the activity since <time ago> grouped by <hour/day/week>
#
# * :conditions
# same as the standard Rails finder. Used to scope your activity to a particular user, etc.
# * :padding
# true/false
# * :group_by
# :hour, :day, :week
#
def activity_since(since=1.week.ago, options={})
# Returns a URL to a simple google chart the represents the activity returned by the plugin
#
# * :type => :bar/:line
# * :size => "200x50"
# * :bgcolor => "EFEFEF"
# * :chart_color => "336699"
# * :area_color => "DFEBFF" (only for :line)
# * :line_color => "0077CC" (only for :line)
# * :line_width => "2" (only for :line)
#
def to_activity_gchart(options={})
Obviously there is not “one index fits all” but you should include an index on at least the timestamp that fits the conditions you are using.
The same applies when performing SUM on a column.
add_index :model, [:created_at]
- Using sum and creating Bar Charts is not fully tested – HouseKeeper
- Some work still needs to be done on the queries and a tidy up of has_activity.rb
Copyright © 2009 Cary Dunn <cary.dunn@gmail.com>, HouseKeeper <carl@house-keeping.com>, released under the MIT license