aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/video/video-query-builder.ts
diff options
context:
space:
mode:
authorRigel Kent <sendmemail@rigelk.eu>2021-01-25 04:32:39 +0100
committerChocobozzz <chocobozzz@cpy.re>2021-01-28 15:55:34 +0100
commit28eeb811c40325a28208231324f66f4032e5cf67 (patch)
tree38b3819b511b311db70c70298cb4da63c207edf3 /server/models/video/video-query-builder.ts
parent5bcbcbe338ef5a1ed14f084311d013fbb25dabcf (diff)
downloadPeerTube-28eeb811c40325a28208231324f66f4032e5cf67.tar.gz
PeerTube-28eeb811c40325a28208231324f66f4032e5cf67.tar.zst
PeerTube-28eeb811c40325a28208231324f66f4032e5cf67.zip
only count comments from people other than the video author
Diffstat (limited to 'server/models/video/video-query-builder.ts')
-rw-r--r--server/models/video/video-query-builder.ts25
1 files changed, 21 insertions, 4 deletions
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)
268 like: 3, 268 like: 3,
269 dislike: 3, 269 dislike: 3,
270 view: 1 / 12, 270 view: 1 / 12,
271 comment: 6 271 comment: 2 // a comment takes more time than a like to do, but can be done multiple times
272 } 272 }
273 273
274 joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"') 274 cte.push( // TODO: exclude blocklisted comments
275 '"totalCommentsWithoutVideoAuthor" AS (' +
276 'SELECT "video"."id", ' +
277 'COUNT("replies"."id") - (' +
278 'SELECT COUNT("authorReplies"."id") ' +
279 'FROM "videoComment" AS "authorReplies" ' +
280 'LEFT JOIN "account" ON "account"."id" = "authorReplies"."accountId" ' +
281 'LEFT JOIN "videoChannel" ON "videoChannel"."accountId" = "account"."id" ' +
282 'WHERE "video"."channelId" = "videoChannel"."id" ' +
283 ') as "value" ' +
284 'FROM "videoComment" AS "replies" ' +
285 'LEFT JOIN "video" ON "video"."id" = "replies"."videoId" ' +
286 'WHERE "replies"."videoId" = "video"."id" ' +
287 'GROUP BY "video"."id"' +
288 ')'
289 )
290
291 joins.push('LEFT JOIN "totalCommentsWithoutVideoAuthor" ON "video"."id" = "totalCommentsWithoutVideoAuthor"."id"')
275 292
276 attributes.push( 293 attributes.push(
277 `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+) 294 `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+)
278 `- LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-) 295 `- LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-)
279 `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+) 296 `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+)
280 `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id") - 1)) * ${weights.comment} ` + // comments (+) 297 `+ LOG(GREATEST(1, "totalCommentsWithoutVideoAuthor"."value")) * ${weights.comment} ` + // comments (+)
281 '+ (SELECT EXTRACT(epoch FROM "video"."publishedAt") / 47000) ' + // base score (in number of half-days) 298 '+ (SELECT EXTRACT(epoch FROM "video"."publishedAt") / 47000) ' + // base score (in number of half-days)
282 'AS "score"' 299 'AS "score"'
283 ) 300 )
284 301
285 group = 'GROUP BY "video"."id"' 302 group = 'GROUP BY "video"."id", "totalCommentsWithoutVideoAuthor"."value"'
286 } 303 }
287 304
288 if (options.historyOfUser) { 305 if (options.historyOfUser) {