Retain historical node_counter information
| Project: | Drupal |
| Version: | 7.x-dev |
| Component: | statistics.module |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Jeremy@kerneltrap.org |
| Status: | patch (code needs work) |
Jump to:
Currently the node_counter table tracks three things: how many times a given node has been viewed since creation, how many times a given node has been viewed in a 24 hour period, and a timestamp for the last time a given node was viewed.
The goal of this patch is to provide historical view information allowing for the creation of useful reports showing how popular a given node has been over time. From this data we could determine not only how many times a node has been viewed since creation, but also how many times it was viewed yesterday, or last week, or two weeks ago, or last month, etc.
I employ a technique that I first used in my ad project, again to collect historical statistics. This patch is not 100% complete -- some additional queries need to be written to get all previous node_counter functionality working with the new schema (volunteers are encouraged to step up -- all known issues needing work are marked with a TODO in the patch).
How it works:
The new ad_counter table looks like:
CREATE TABLE {node_counter} (
nid int NOT NULL default '0',
count bigint unsigned NOT NULL default '0',
timestamp int(10) unsigned NOT NULL default '0',
PRIMARY KEY (nid,timestamp)
) /*!40100 DEFAULT CHARACTER SET UTF8 */To update the table, we now employ the following logic:
if ((arg(0) == 'node') && is_numeric(arg(1)) && (arg(2) == '' || arg(2) == 'view')) {
// A node has been viewed, so update the node's counters.
db_query('UPDATE {node_counter} SET count = count + 1 WHERE nid = %d AND timestamp = %d', arg(1), date('YmdH'));
// If we affected 0 rows, this is the first time viewing the node.
if (!db_affected_rows()) {
// We must create a new row to store counters for the new node.
db_query('INSERT INTO {node_counter} (nid, count, timestamp) VALUES (%d, 1, %d)', arg(1), date('YmdH'));
}
}Thus, whereas before we had one counter for views since creation, we now have one counter for every hour since creation. To gather the total views, we simply execute the following query:
db_result(db_query('SELECT SUM(count) FROM {node_counter} WHERE nid = %d', $nid));To gather the total views from today we execute the following query:
db_result(db_query('SELECT SUM(count) FROM {node_counter} WHERE nid = %d AND timestamp >= %d AND timestamp <= %d', $nid, date('Ymd00'), date('Ymd23')));Get a little more creative, and you can gather views from any time period down to a one hour granularity. With this data, for example, one could generate very interesting charts showing the popularity of a node over time. (Refer to the ad module's ad_report module for examples of generating reports with this type of data.)
Concerns:
- Perhaps the hourly granularity is too much? Would a one day granularity be preferred? Simple enough, change to date('Ymd'). Personally I prefer the hourly granularity as it allows for detailed reports. With the old node_counter table we added one row per node. If we have a daily granularity we add 365 rows per node per year. If we have an hourly granularity we add 8760 rows per node per year.
- Simple reports like "ten most popular nodes" becomes more complicated now -- I imagine we'll either need to use a temporary table or a subquery. Anyone interested in implementing this is encouraged to update the patch -- refer to the areas labeled with a TODO.
| Attachment | Size |
|---|---|
| node_counter.patch | 6.84 KB |
#1
Updating to reflect that the previously attached patch is for review.
#2
needs re-roll for schema api, and won't make it into drupal 6.
#3
Assigning to myself, reminding myself to re-roll this patch soon.