only count comments from people other than the video author

2 files changed, 22 insertions, 4 deletions

diff --git a/client/src/app/+admin/config/edit-custom-config/edit-custom-config.component.html b/client/src/app/+admin/config/edit-custom-config/edit-custom-config.component.html
index 09e7e96ac..9e4691670 100644
--- a/client/src/app/+admin/config/edit-custom-config/edit-custom-config.component.html
+++ b/client/src/app/+admin/config/edit-custom-config/edit-custom-config.component.html
@@ -270,6 +270,7 @@
                 <select id="instanceDefaultClientRoute" formControlName="defaultClientRoute" class="form-control">
                   <option i18n value="/videos/overview">Discover videos</option>
                   <option i18n value="/videos/trending">Trending videos</option>
+                  <option i18n value="/videos/hot">Hot videos</option>
                   <option i18n value="/videos/most-liked">Most liked videos</option>
                   <option i18n value="/videos/recently-added">Recently added videos</option>
                   <option i18n value="/videos/local">Local videos</option>
diff --git a/server/models/video/video-query-builder.ts b/server/models/video/video-query-builder.ts
index 65b72fe1c..8e0965244 100644
--- a/server/models/video/video-query-builder.ts
+++ b/server/models/video/video-query-builder.ts
@@ -268,21 +268,38 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions)
       like: 3,
       dislike: 3,
       view: 1 / 12,
-      comment: 6
+      comment: 2 // a comment takes more time than a like to do, but can be done multiple times
     }
 
-    joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"')
+    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"' +
+      ')'
+    )
+
+    joins.push('LEFT JOIN "totalCommentsWithoutVideoAuthor" ON "video"."id" = "totalCommentsWithoutVideoAuthor"."id"')
 
     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") - 1)) * ${weights.comment} ` + // comments (+)
+      `+ LOG(GREATEST(1, "totalCommentsWithoutVideoAuthor"."value")) * ${weights.comment} ` + // comments (+)
       '+ (SELECT EXTRACT(epoch FROM "video"."publishedAt") / 47000) ' + // base score (in number of half-days)
       'AS "score"'
     )
 
-    group = 'GROUP BY "video"."id"'
+    group = 'GROUP BY "video"."id", "totalCommentsWithoutVideoAuthor"."value"'
   }
 
   if (options.historyOfUser) {