From 3da68f0a781ebd893521e2e6fa200280c92ae815 Mon Sep 17 00:00:00 2001
From: Rigel Kent <sendmemail@rigelk.eu>
Date: Tue, 26 Jan 2021 01:53:13 +0100
Subject: add default trending page choice, revert comments count for hot
 strategy

---
 server/models/video/video-query-builder.ts | 95 +++++++++++++-----------------
 1 file changed, 40 insertions(+), 55 deletions(-)

(limited to 'server/models/video')

diff --git a/server/models/video/video-query-builder.ts b/server/models/video/video-query-builder.ts
index 8e0965244..3f31ac862 100644
--- a/server/models/video/video-query-builder.ts
+++ b/server/models/video/video-query-builder.ts
@@ -242,64 +242,49 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions)
   }
 
   // We don't exclude results in this so if we do a count we don't need to add this complex clause
-  if (options.trendingDays && options.isCount !== true) {
-    const viewsGteDate = new Date(new Date().getTime() - (24 * 3600 * 1000) * options.trendingDays)
-
-    joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate')
-    replacements.viewsGteDate = viewsGteDate
-
-    attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "score"')
-
-    group = 'GROUP BY "video"."id"'
-  } else if (options.hot && options.isCount !== true) {
-    /**
-     * "Hotness" is a measure based on absolute view/comment/like/dislike numbers,
-     * with fixed weights only applied to their log values.
-     *
-     * This algorithm gives little chance for an old video to have a good score,
-     * for which recent spikes in interactions could be a sign of "hotness" and
-     * justify a better score. However there are multiple ways to achieve that
-     * goal, which is left for later. Yes, this is a TODO :)
-     *
-     * note: weights and base score are in number of half-days.
-     * see https://github.com/reddit-archive/reddit/blob/master/r2/r2/lib/db/_sorts.pyx#L47-L58
-     */
-    const weights = {
-      like: 3,
-      dislike: 3,
-      view: 1 / 12,
-      comment: 2 // a comment takes more time than a like to do, but can be done multiple times
-    }
-
-    cte.push( // TODO: exclude blocklisted comments
-      '"totalCommentsWithoutVideoAuthor" AS (' +
-        'SELECT "video"."id", ' +
-                'COUNT("replies"."id") - (' +
-                  'SELECT COUNT("authorReplies"."id") ' +
-                  'FROM "videoComment" AS "authorReplies" ' +
-                  'LEFT JOIN "account" ON "account"."id" = "authorReplies"."accountId" ' +
-                  'LEFT JOIN "videoChannel" ON "videoChannel"."accountId" = "account"."id" ' +
-                  'WHERE "video"."channelId" = "videoChannel"."id" ' +
-                ') as "value" ' +
-        'FROM "videoComment" AS "replies" ' +
-        'LEFT JOIN "video" ON "video"."id" = "replies"."videoId" ' +
-        'WHERE "replies"."videoId" = "video"."id" ' +
-        'GROUP BY "video"."id"' +
-      ')'
-    )
+  if (options.isCount !== true) {
+    if (options.trendingDays) {
+      const viewsGteDate = new Date(new Date().getTime() - (24 * 3600 * 1000) * options.trendingDays)
+
+      joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate')
+      replacements.viewsGteDate = viewsGteDate
+
+      attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "score"')
+
+      group = 'GROUP BY "video"."id"'
+    } else if (options.hot) {
+      /**
+       * "Hotness" is a measure based on absolute view/comment/like/dislike numbers,
+       * with fixed weights only applied to their log values.
+       *
+       * This algorithm gives little chance for an old video to have a good score,
+       * for which recent spikes in interactions could be a sign of "hotness" and
+       * justify a better score. However there are multiple ways to achieve that
+       * goal, which is left for later. Yes, this is a TODO :)
+       *
+       * note: weights and base score are in number of half-days.
+       * see https://github.com/reddit-archive/reddit/blob/master/r2/r2/lib/db/_sorts.pyx#L47-L58
+       */
+      const weights = {
+        like: 3,
+        dislike: 3,
+        view: 1 / 12,
+        comment: 2 // a comment takes more time than a like to do, but can be done multiple times
+      }
 
-    joins.push('LEFT JOIN "totalCommentsWithoutVideoAuthor" ON "video"."id" = "totalCommentsWithoutVideoAuthor"."id"')
+      joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"')
 
-    attributes.push(
-      `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+)
-      `- LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-)
-      `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+)
-      `+ LOG(GREATEST(1, "totalCommentsWithoutVideoAuthor"."value")) * ${weights.comment} ` + // comments (+)
-      '+ (SELECT EXTRACT(epoch FROM "video"."publishedAt") / 47000) ' + // base score (in number of half-days)
-      'AS "score"'
-    )
+      attributes.push(
+        `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+)
+        `- LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-)
+        `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+)
+        `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id"))) * ${weights.comment} ` + // comments (+)
+        '+ (SELECT EXTRACT(epoch FROM "video"."publishedAt") / 47000) ' + // base score (in number of half-days)
+        'AS "score"'
+      )
 
-    group = 'GROUP BY "video"."id", "totalCommentsWithoutVideoAuthor"."value"'
+      group = 'GROUP BY "video"."id"'
+    }
   }
 
   if (options.historyOfUser) {
-- 
cgit v1.2.3