aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/video/video-query-builder.ts
diff options
context:
space:
mode:
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) {