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.ts33
1 files changed, 22 insertions, 11 deletions
diff --git a/server/models/video/video-query-builder.ts b/server/models/video/video-query-builder.ts
index e2145fb9a..822d0c89b 100644
--- a/server/models/video/video-query-builder.ts
+++ b/server/models/video/video-query-builder.ts
@@ -31,8 +31,8 @@ export type BuildVideosQueryOptions = {
31 31
32 videoPlaylistId?: number 32 videoPlaylistId?: number
33 33
34 trendingAlgorithm?: string // best, hot, or any other algorithm implemented
34 trendingDays?: number 35 trendingDays?: number
35 hot?: boolean
36 36
37 user?: MUserAccountId 37 user?: MUserAccountId
38 historyOfUser?: MUserId 38 historyOfUser?: MUserId
@@ -252,7 +252,7 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions)
252 attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "score"') 252 attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "score"')
253 253
254 group = 'GROUP BY "video"."id"' 254 group = 'GROUP BY "video"."id"'
255 } else if (options.hot) { 255 } else if ([ 'best', 'hot' ].includes(options.trendingAlgorithm)) {
256 /** 256 /**
257 * "Hotness" is a measure based on absolute view/comment/like/dislike numbers, 257 * "Hotness" is a measure based on absolute view/comment/like/dislike numbers,
258 * with fixed weights only applied to their log values. 258 * with fixed weights only applied to their log values.
@@ -269,28 +269,39 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions)
269 */ 269 */
270 const weights = { 270 const weights = {
271 like: 3, 271 like: 3,
272 dislike: 3, 272 dislike: -3,
273 view: 1 / 12, 273 view: 1 / 12,
274 comment: 2 // a comment takes more time than a like to do, but can be done multiple times 274 comment: 2, // a comment takes more time than a like to do, but can be done multiple times
275 history: -2
275 } 276 }
276 277
277 joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"') 278 joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"')
278 279
279 attributes.push( 280 let attribute =
280 `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+) 281 `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+)
281 `- LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-) 282 `+ LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-)
282 `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+) 283 `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+)
283 `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id"))) * ${weights.comment} ` + // comments (+) 284 `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id"))) * ${weights.comment} ` + // comments (+)
284 '+ (SELECT EXTRACT(epoch FROM "video"."publishedAt") / 47000) ' + // base score (in number of half-days) 285 '+ (SELECT EXTRACT(epoch FROM "video"."publishedAt") / 47000) ' // base score (in number of half-days)
285 'AS "score"' 286
286 ) 287 if (options.trendingAlgorithm === 'best' && options.user) {
288 joins.push(
289 'LEFT JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :bestUser'
290 )
291 replacements.bestUser = options.user.id
292
293 attribute += `+ POWER(COUNT(DISTINCT "userVideoHistory"."id"), 2.0) * ${weights.history} `
294 }
295
296 attribute += 'AS "score"'
297 attributes.push(attribute)
287 298
288 group = 'GROUP BY "video"."id"' 299 group = 'GROUP BY "video"."id"'
289 } 300 }
290 } 301 }
291 302
292 if (options.historyOfUser) { 303 if (options.historyOfUser) {
293 joins.push('INNER JOIN "userVideoHistory" on "video"."id" = "userVideoHistory"."videoId"') 304 joins.push('INNER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId"')
294 305
295 and.push('"userVideoHistory"."userId" = :historyOfUser') 306 and.push('"userVideoHistory"."userId" = :historyOfUser')
296 replacements.historyOfUser = options.historyOfUser.id 307 replacements.historyOfUser = options.historyOfUser.id
@@ -410,7 +421,7 @@ function buildOrder (value: string) {
410 421
411 if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()' 422 if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()'
412 423
413 if ([ 'trending', 'hot' ].includes(field.toLowerCase())) { // Sort by aggregation 424 if ([ 'trending', 'hot', 'best' ].includes(field.toLowerCase())) { // Sort by aggregation
414 return `ORDER BY "score" ${direction}, "video"."views" ${direction}` 425 return `ORDER BY "score" ${direction}, "video"."views" ${direction}`
415 } 426 }
416 427