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.

1 files changed, 3 insertions, 8 deletions

diff --git a/lib/personaltagcloudsection.php b/lib/personaltagcloudsection.php
index 0b29d58ca..091425f92 100644
--- a/lib/personaltagcloudsection.php
+++ b/lib/personaltagcloudsection.php
@@ -58,13 +58,9 @@ class PersonalTagCloudSection extends TagCloudSection
 
     function getTags()
     {
-        if (common_config('db', 'type') == 'pgsql') {
-            $weightexpr='sum(exp(-extract(epoch from (now() - notice_tag.created)) / %s))';
-        } else {
-            $weightexpr='sum(exp(-(now() - notice_tag.created) / %s))';
-        }
- 
-       $qry = 'SELECT notice_tag.tag, '.
+        $weightexpr = common_sql_weight('notice_tag.created', common_config('tag', 'dropoff'));
+
+        $qry = 'SELECT notice_tag.tag, '.
           $weightexpr . ' as weight ' .
           'FROM notice_tag JOIN notice ' .
           'ON notice_tag.notice_id = notice.id ' .
@@ -83,7 +79,6 @@ class PersonalTagCloudSection extends TagCloudSection
 
         $tag = Memcached_DataObject::cachedQuery('Notice_tag',
                                                  sprintf($qry,
-                                                         common_config('tag', 'dropoff'),
                                                          $this->user->id),
                                                  3600);
         return $tag;