From 6911e1c7972c3adec53d0fe04ebdd7da0fbd8b12 Mon Sep 17 00:00:00 2001
From: Brion Vibber <brion@status.net>
Date: Mon, 4 Jan 2010 11:55:27 -0800
Subject: Ticket 2141: bugs with weighted popularity lists across year
 boundary.

Consolidated several separate implementations of the same weighting algorithm into common_sql_weight() and fixed some bugs...
For MySQL, now using timestampdiff() instead of subtraction for the comparison, so we get sane results when the year doesn't match, and utc_timestamp() rather than now() so we don't get negative ages for recent items with local server timezone.
Unknown whether the same problems affect PostgreSQL, but note that it lacks the timestampdiff() SQL function.
---
 actions/favorited.php      |  8 ++------
 actions/publictagcloud.php | 11 ++++-------
 2 files changed, 6 insertions(+), 13 deletions(-)

(limited to 'actions')

diff --git a/actions/favorited.php b/actions/favorited.php
index 150b67b0b..9ffa5b844 100644
--- a/actions/favorited.php
+++ b/actions/favorited.php
@@ -185,11 +185,7 @@ class FavoritedAction extends Action
 
     function showContent()
     {
-        if (common_config('db', 'type') == 'pgsql') {
-            $weightexpr='sum(exp(-extract(epoch from (now() - fave.modified)) / %s))';
-        } else {
-            $weightexpr='sum(exp(-(now() - fave.modified) / %s))';
-        }
+        $weightexpr = common_sql_weight('fave.modified', common_config('popular', 'dropoff'));
 
         $qry = 'SELECT notice.*, '.
           $weightexpr . ' as weight ' .
@@ -207,7 +203,7 @@ class FavoritedAction extends Action
         }
 
         $notice = Memcached_DataObject::cachedQuery('Notice',
-                                                    sprintf($qry, common_config('popular', 'dropoff')),
+                                                    $qry,
                                                     600);
 
         $nl = new NoticeList($notice, $this);
diff --git a/actions/publictagcloud.php b/actions/publictagcloud.php
index 5c7074029..b5b474f13 100644
--- a/actions/publictagcloud.php
+++ b/actions/publictagcloud.php
@@ -105,12 +105,8 @@ class PublictagcloudAction extends Action
 
         #Add the aggregated columns...
         $tags->selectAdd('max(notice_id) as last_notice_id');
-        if(common_config('db','type')=='pgsql') {
-            $calc='sum(exp(-extract(epoch from (now()-created))/%s)) as weight';
-        } else {
-            $calc='sum(exp(-(now() - created)/%s)) as weight';
-        }
-        $tags->selectAdd(sprintf($calc, common_config('tag', 'dropoff')));
+        $calc = common_sql_weight('created', common_config('tag', 'dropoff'));
+        $tags->selectAdd($calc . ' as weight');
         $tags->groupBy('tag');
         $tags->orderBy('weight DESC');
 
@@ -136,10 +132,11 @@ class PublictagcloudAction extends Action
             $this->elementStart('dd');
             $this->elementStart('ul', 'tags xoxo tag-cloud');
             foreach ($tw as $tag => $weight) {
+                common_log(LOG_DEBUG, "$weight/$sum");
                 if ($sum) {
                     $weightedSum = $weight/$sum;
                 } else {
-                    $weightedSum = 1;
+                    $weightedSum = 0.5;
                 }
                 $this->showTag($tag, $weight, $weightedSum);
             }
-- 
cgit v1.2.3-54-g00ecf