Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.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.
| Comment | File | Size | Author |
|---|---|---|---|
| node_counter.patch | 6.84 KB | Jeremy | |
Comments
Comment #1
Jeremy CreditAttribution: Jeremy commentedUpdating to reflect that the previously attached patch is for review.
Comment #2
catchneeds re-roll for schema api, and won't make it into drupal 6.
Comment #3
Jeremy CreditAttribution: Jeremy commentedAssigning to myself, reminding myself to re-roll this patch soon.
Comment #4
Dave ReidI think probably the easiest thing to do is to create a table with just nid and timestamp. Everytime a node is viewed, add a record to the table. To get the total number of views, just run
SELECT COUNT(nid) FROM {node_counter} WHERE nid = :nid GROUP BY nid. You can then archive the data by selecting the number of records between two timestamps (a day, a week, etc).Comment #5
moshe weitzman CreditAttribution: moshe weitzman commentedI have needed this several times. I ended up hacking core so that its 24 counter really meant 1 week counter. Subscribe.
Comment #6
Dave ReidComment #7
yoroy CreditAttribution: yoroy commented#488550: [PP-1] D7UX Microproject - Dashboard Widget for Traffic Data is postponed on this one
Comment #13
Wim LeersI've been using your https://www.drupal.org/project/ad module for more than 5 years now, maybe even 8. (On a Drupal 6 site.) That has worked amazingly well.
This is going to yield an enormous number of rows. Plus, there's more potential for privacy concerns. Why is hour granularity as proposed in the IS not sufficient?
More importantly: do we still want this to happen?
Comment #14
Jeremy CreditAttribution: Jeremy at Tag1 Consulting commentedI had forgotten all about this!
With core's custom bootstrap in
core/modules/statistics/statistics.phpfor recording views, perhaps it would make most sense to add this as an additional option, instead of replacing existing functionality. ie, preserve the existingdaycountandtotalcountfields, and also record the more granular view data to a new table. Then an option as to what counters to collect. Thetotalcountin particular would be a handy denormalization for some reports, though technically not required.Comment #22
quietone CreditAttribution: quietone at PreviousNext commentedStatistics is approved for removal. See #3266457: [Policy] Deprecate Statistics module in D10 and move to contrib in D11
This is now Postponed. The status is set according to two policies. The Remove a core extension and move it to a contributed project and the Extensions approved for removal policies.
It will be moved to a contributed Statistics project once the project is created and the Drupal 11 branch is open.