The Stale Date

The problem: some articles stick around on a top articles widget (see graphic) for too long. How do you indicate that the article should disappear from the widget and give way to newer content?

List of top 5 popular articles on the enRoute travel site. The second article has been on the list for over 3 months.

One solution to is Digg’s model of decay: over time, an article’s value diminishes and it falls in rank giving way to newer, popular articles. This model is great but requires advanced systems to apply the decay and the decay value may be hard to tweak for non-admins.

Another solution is to give each article a stale date field. When the current date passes the stale date, the article no longer appears in the top articles widget. If your CMS has an end date field for articles, they may be used to the same effect.

In addition, a default stale interval may be specified so that articles without a stale date will become stale after a pre-determined amount of time. A value of 30 days seems like a reasonable amount.

Now for the MySQL side of things…

Lets assume the following:

• The table articles has the fields: id, publish_date (the date it was published) and stale_date
• The table articles_views has the fields id and article_id and is a 1 row/view recording system
• The stale period is set to 30 days

The following SQL statement retrieves all articles in order of their view count:

SELECT a.*, COUNT(v.id) AS `vcount`
FROM articles_views v
LEFT JOIN articles a ON a.id = v.article_id
GROUP BY v.article_id
ORDER BY `vcount` DESC
LIMIT 5

To implement the stale system, you need to move some things around:

SELECT a . * , COUNT( v.id ) AS `vcount`
FROM articles_views v
RIGHT JOIN articles a ON a.id = v.article_id AND
    ((a.stale_date IS NULL AND DATEDIFF(CURDATE(), a.published_date) <= 30)
    OR (a.stale_date > CURDATE()))
GROUP BY v.article_id
ORDER BY `vcount` DESC
LIMIT 5

Note how instead of LEFT JOINing the articles table, we RIGHT JOIN it. Also, on line 4, we test to see that either there is no stale date and the published date is less than 30 days behind or that the current date is not greater than the stale date (line 5).

Depending on your publishing schedule, you may wish to increase the default stale interval to a value greater than 30 days. For instance, 3 months (90 days), half a year (182 days) or a full year (365 days).

Does your CMS handle staleness in articles? If so, how?