aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/video/video-query-builder.ts
diff options
context:
space:
mode:
authorRigel Kent <sendmemail@rigelk.eu>2021-01-22 00:12:44 +0100
committerChocobozzz <chocobozzz@cpy.re>2021-01-28 15:55:34 +0100
commit5bcbcbe338ef5a1ed14f084311d013fbb25dabcf (patch)
treeb0f6382b30b67f1f7adddaf7d12af9adae0c9f5d /server/models/video/video-query-builder.ts
parent7a4994873c0b3394d04e16e877fc7418bc8b146a (diff)
downloadPeerTube-5bcbcbe338ef5a1ed14f084311d013fbb25dabcf.tar.gz
PeerTube-5bcbcbe338ef5a1ed14f084311d013fbb25dabcf.tar.zst
PeerTube-5bcbcbe338ef5a1ed14f084311d013fbb25dabcf.zip
modularize abstract video list header and implement video hotness recommendation variant
Diffstat (limited to 'server/models/video/video-query-builder.ts')
-rw-r--r--server/models/video/video-query-builder.ts42
1 files changed, 38 insertions, 4 deletions
diff --git a/server/models/video/video-query-builder.ts b/server/models/video/video-query-builder.ts
index 9e5b6febb..65b72fe1c 100644
--- a/server/models/video/video-query-builder.ts
+++ b/server/models/video/video-query-builder.ts
@@ -32,6 +32,8 @@ export type BuildVideosQueryOptions = {
32 videoPlaylistId?: number 32 videoPlaylistId?: number
33 33
34 trendingDays?: number 34 trendingDays?: number
35 hot?: boolean
36
35 user?: MUserAccountId 37 user?: MUserAccountId
36 historyOfUser?: MUserId 38 historyOfUser?: MUserId
37 39
@@ -239,14 +241,46 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions)
239 } 241 }
240 } 242 }
241 243
242 // We don't exclude results in this if so if we do a count we don't need to add this complex clauses 244 // We don't exclude results in this so if we do a count we don't need to add this complex clause
243 if (options.trendingDays && options.isCount !== true) { 245 if (options.trendingDays && options.isCount !== true) {
244 const viewsGteDate = new Date(new Date().getTime() - (24 * 3600 * 1000) * options.trendingDays) 246 const viewsGteDate = new Date(new Date().getTime() - (24 * 3600 * 1000) * options.trendingDays)
245 247
246 joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate') 248 joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate')
247 replacements.viewsGteDate = viewsGteDate 249 replacements.viewsGteDate = viewsGteDate
248 250
249 attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "videoViewsSum"') 251 attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "score"')
252
253 group = 'GROUP BY "video"."id"'
254 } else if (options.hot && options.isCount !== true) {
255 /**
256 * "Hotness" is a measure based on absolute view/comment/like/dislike numbers,
257 * with fixed weights only applied to their log values.
258 *
259 * This algorithm gives little chance for an old video to have a good score,
260 * for which recent spikes in interactions could be a sign of "hotness" and
261 * justify a better score. However there are multiple ways to achieve that
262 * goal, which is left for later. Yes, this is a TODO :)
263 *
264 * note: weights and base score are in number of half-days.
265 * see https://github.com/reddit-archive/reddit/blob/master/r2/r2/lib/db/_sorts.pyx#L47-L58
266 */
267 const weights = {
268 like: 3,
269 dislike: 3,
270 view: 1 / 12,
271 comment: 6
272 }
273
274 joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"')
275
276 attributes.push(
277 `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+)
278 `- LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-)
279 `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+)
280 `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id") - 1)) * ${weights.comment} ` + // comments (+)
281 '+ (SELECT EXTRACT(epoch FROM "video"."publishedAt") / 47000) ' + // base score (in number of half-days)
282 'AS "score"'
283 )
250 284
251 group = 'GROUP BY "video"."id"' 285 group = 'GROUP BY "video"."id"'
252 } 286 }
@@ -372,8 +406,8 @@ function buildOrder (value: string) {
372 406
373 if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()' 407 if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()'
374 408
375 if (field.toLowerCase() === 'trending') { // Sort by aggregation 409 if ([ 'trending', 'hot' ].includes(field.toLowerCase())) { // Sort by aggregation
376 return `ORDER BY "videoViewsSum" ${direction}, "video"."views" ${direction}` 410 return `ORDER BY "score" ${direction}, "video"."views" ${direction}`
377 } 411 }
378 412
379 let firstSort: string 413 let firstSort: string