Contrib.social

Retain historical node_counter information

Open on Drupal.org →
Created on 9 May 2007, over 17 years ago
Updated 17 August 2024, 2 months ago

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.

<!--break-->

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).

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.)

  • 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.
✨ Feature request
Status

Needs work

Version

1.0

Component
Statistics  →

Last updated 3 days ago

No maintainer
Created by

🇮🇹Italy Jeremy Tuscany

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

  • Comment over 1 year ago →
    🇳🇿New Zealand quietone

    Statistics is approved for removal. See 🌱 [Policy] Deprecate Statistics module in D10 and move to contrib in D11 Fixed

    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.

  • Status changed to Needs work 6 months ago
  • Comment 6 months ago →
    🇳🇿New Zealand quietone
  • Comment 2 months ago →
    🇨🇦Canada ciesinsg

    I found this issue while trying to determine if this is possible with the statistics module, however upon noticing that this is over 17 years old and has been put back into "Needs work" four months ago, I am not sure what is the status of this feature. Is this feature currently in limbo?

    If it is, does anybody know if there is an in-progress branch, recent patch compatible with this version of statistics & Drupal, or a module that extends statistics so that it counts the views per day. I would volunteer to work on this, but I don't have any experience writing modules that use queries and I'm not confident I could create something like this yet.

  • Comment 2 months ago →
    🇫🇷France fgm Paris, France

    In order to maintain DB size to reasonable levels, the statistics module is not keeping any kind of history in the Drupal DB.

    As described in the Plan at 🌱 Plan for Statistics 1.x Active , this is a stretch goal, and currently implemented by additional non-Drupal code using external databases best suited to the kind of volume needed by analytics.

    This is actually part of the Plan issue: Statistics is expected to provide basic data collection services for instantaneous statistics at low cost, and provide the statistics collection point for third party "ecosystem" software providing extra features while not exposing the actual Drupal DB to them for PII protection reasons.

  • Comment 3 days ago →
    🇫🇷France prudloff Lille

    Not exactly the same as the requested feature, I am working on a module that keeps a daily view count history over a rolling period: https://www.drupal.org/project/statistics_rolling →

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024