From 28eeb811c40325a28208231324f66f4032e5cf67 Mon Sep 17 00:00:00 2001 From: Rigel Kent Date: Mon, 25 Jan 2021 04:32:39 +0100 Subject: only count comments from people other than the video author --- server/models/video/video-query-builder.ts | 25 +++++++++++++++++++++---- 1 file changed, 21 insertions(+), 4 deletions(-) (limited to 'server') 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) { -- cgit v1.2.3