aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/video
diff options
context:
space:
mode:
Diffstat (limited to 'server/models/video')
-rw-r--r--server/models/video/video-query-builder.ts95
1 files changed, 40 insertions, 55 deletions
diff --git a/server/models/video/video-query-builder.ts b/server/models/video/video-query-builder.ts
index 8e0965244..3f31ac862 100644
--- a/server/models/video/video-query-builder.ts
+++ b/server/models/video/video-query-builder.ts
@@ -242,64 +242,49 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions)
242 } 242 }
243 243
244 // We don't exclude results in this so if we do a count we don't need to add this complex clause 244 // We don't exclude results in this so if we do a count we don't need to add this complex clause
245 if (options.trendingDays && options.isCount !== true) { 245 if (options.isCount !== true) {
246 const viewsGteDate = new Date(new Date().getTime() - (24 * 3600 * 1000) * options.trendingDays) 246 if (options.trendingDays) {
247 247 const viewsGteDate = new Date(new Date().getTime() - (24 * 3600 * 1000) * options.trendingDays)
248 joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate') 248
249 replacements.viewsGteDate = viewsGteDate 249 joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate')
250 250 replacements.viewsGteDate = viewsGteDate
251 attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "score"') 251
252 252 attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "score"')
253 group = 'GROUP BY "video"."id"' 253
254 } else if (options.hot && options.isCount !== true) { 254 group = 'GROUP BY "video"."id"'
255 /** 255 } else if (options.hot) {
256 * "Hotness" is a measure based on absolute view/comment/like/dislike numbers, 256 /**
257 * with fixed weights only applied to their log values. 257 * "Hotness" is a measure based on absolute view/comment/like/dislike numbers,
258 * 258 * with fixed weights only applied to their log values.
259 * This algorithm gives little chance for an old video to have a good score, 259 *
260 * for which recent spikes in interactions could be a sign of "hotness" and 260 * This algorithm gives little chance for an old video to have a good score,
261 * justify a better score. However there are multiple ways to achieve that 261 * for which recent spikes in interactions could be a sign of "hotness" and
262 * goal, which is left for later. Yes, this is a TODO :) 262 * justify a better score. However there are multiple ways to achieve that
263 * 263 * goal, which is left for later. Yes, this is a TODO :)
264 * note: weights and base score are in number of half-days. 264 *
265 * see https://github.com/reddit-archive/reddit/blob/master/r2/r2/lib/db/_sorts.pyx#L47-L58 265 * note: weights and base score are in number of half-days.
266 */ 266 * see https://github.com/reddit-archive/reddit/blob/master/r2/r2/lib/db/_sorts.pyx#L47-L58
267 const weights = { 267 */
268 like: 3, 268 const weights = {
269 dislike: 3, 269 like: 3,
270 view: 1 / 12, 270 dislike: 3,
271 comment: 2 // a comment takes more time than a like to do, but can be done multiple times 271 view: 1 / 12,
272 } 272 comment: 2 // a comment takes more time than a like to do, but can be done multiple times
273 273 }
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 274
291 joins.push('LEFT JOIN "totalCommentsWithoutVideoAuthor" ON "video"."id" = "totalCommentsWithoutVideoAuthor"."id"') 275 joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"')
292 276
293 attributes.push( 277 attributes.push(
294 `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+) 278 `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+)
295 `- LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-) 279 `- LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-)
296 `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+) 280 `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+)
297 `+ LOG(GREATEST(1, "totalCommentsWithoutVideoAuthor"."value")) * ${weights.comment} ` + // comments (+) 281 `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id"))) * ${weights.comment} ` + // comments (+)
298 '+ (SELECT EXTRACT(epoch FROM "video"."publishedAt") / 47000) ' + // base score (in number of half-days) 282 '+ (SELECT EXTRACT(epoch FROM "video"."publishedAt") / 47000) ' + // base score (in number of half-days)
299 'AS "score"' 283 'AS "score"'
300 ) 284 )
301 285
302 group = 'GROUP BY "video"."id", "totalCommentsWithoutVideoAuthor"."value"' 286 group = 'GROUP BY "video"."id"'
287 }
303 } 288 }
304 289
305 if (options.historyOfUser) { 290 if (options.historyOfUser) {