server/models/video/sql/video/videos-id-list-query-builder.ts

   1 import { Sequelize, Transaction } from 'sequelize'
   2 import validator from 'validator'
   3 import { exists } from '@server/helpers/custom-validators/misc'
   4 import { WEBSERVER } from '@server/initializers/constants'
   5 import { buildSortDirectionAndField } from '@server/models/shared'
   6 import { MUserAccountId, MUserId } from '@server/types/models'
   7 import { forceNumber } from '@shared/core-utils'
   8 import { VideoInclude, VideoPrivacy, VideoState } from '@shared/models'
   9 import { createSafeIn, parseRowCountResult } from '../../../shared'
  10 import { AbstractRunQuery } from '../../../shared/abstract-run-query'
  11
  12 /**
  13  *
  14  * Build videos list SQL query to fetch rows
  15  *
  16  */
  17
  18 export type DisplayOnlyForFollowerOptions = {
  19   actorId: number
  20   orLocalVideos: boolean
  21 }
  22
  23 export type BuildVideosListQueryOptions = {
  24   attributes?: string[]
  25
  26   serverAccountIdForBlock: number
  27
  28   displayOnlyForFollower: DisplayOnlyForFollowerOptions
  29
  30   count: number
  31   start: number
  32   sort: string
  33
  34   nsfw?: boolean
  35   host?: string
  36   isLive?: boolean
  37   isLocal?: boolean
  38   include?: VideoInclude
  39
  40   categoryOneOf?: number[]
  41   licenceOneOf?: number[]
  42   languageOneOf?: string[]
  43   tagsOneOf?: string[]
  44   tagsAllOf?: string[]
  45   privacyOneOf?: VideoPrivacy[]
  46
  47   uuids?: string[]
  48
  49   hasFiles?: boolean
  50   hasHLSFiles?: boolean
  51   hasWebtorrentFiles?: boolean
  52
  53   accountId?: number
  54   videoChannelId?: number
  55
  56   videoPlaylistId?: number
  57
  58   trendingAlgorithm?: string // best, hot, or any other algorithm implemented
  59   trendingDays?: number
  60
  61   user?: MUserAccountId
  62   historyOfUser?: MUserId
  63
  64   startDate?: string // ISO 8601
  65   endDate?: string // ISO 8601
  66   originallyPublishedStartDate?: string
  67   originallyPublishedEndDate?: string
  68
  69   durationMin?: number // seconds
  70   durationMax?: number // seconds
  71
  72   search?: string
  73
  74   isCount?: boolean
  75
  76   group?: string
  77   having?: string
  78
  79   transaction?: Transaction
  80   logging?: boolean
  81 }
  82
  83 export class VideosIdListQueryBuilder extends AbstractRunQuery {
  84   protected replacements: any = {}
  85
  86   private attributes: string[]
  87   private joins: string[] = []
  88
  89   private readonly and: string[] = []
  90
  91   private readonly cte: string[] = []
  92
  93   private group = ''
  94   private having = ''
  95
  96   private sort = ''
  97   private limit = ''
  98   private offset = ''
  99
 100   constructor (protected readonly sequelize: Sequelize) {
 101     super(sequelize)
 102   }
 103
 104   queryVideoIds (options: BuildVideosListQueryOptions) {
 105     this.buildIdsListQuery(options)
 106
 107     return this.runQuery()
 108   }
 109
 110   countVideoIds (countOptions: BuildVideosListQueryOptions): Promise<number> {
 111     this.buildIdsListQuery(countOptions)
 112
 113     return this.runQuery().then(rows => parseRowCountResult(rows))
 114   }
 115
 116   getQuery (options: BuildVideosListQueryOptions) {
 117     this.buildIdsListQuery(options)
 118
 119     return { query: this.query, sort: this.sort, replacements: this.replacements }
 120   }
 121
 122   private buildIdsListQuery (options: BuildVideosListQueryOptions) {
 123     this.attributes = options.attributes || [ '"video"."id"' ]
 124
 125     if (options.group) this.group = options.group
 126     if (options.having) this.having = options.having
 127
 128     this.joins = this.joins.concat([
 129       'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"',
 130       'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"',
 131       'INNER JOIN "actor" "accountActor" ON "account"."actorId" = "accountActor"."id"'
 132     ])
 133
 134     if (!(options.include & VideoInclude.BLACKLISTED)) {
 135       this.whereNotBlacklisted()
 136     }
 137
 138     if (options.serverAccountIdForBlock && !(options.include & VideoInclude.BLOCKED_OWNER)) {
 139       this.whereNotBlocked(options.serverAccountIdForBlock, options.user)
 140     }
 141
 142     // Only list published videos
 143     if (!(options.include & VideoInclude.NOT_PUBLISHED_STATE)) {
 144       this.whereStateAvailable()
 145     }
 146
 147     if (options.videoPlaylistId) {
 148       this.joinPlaylist(options.videoPlaylistId)
 149     }
 150
 151     if (exists(options.isLocal)) {
 152       this.whereLocal(options.isLocal)
 153     }
 154
 155     if (options.host) {
 156       this.whereHost(options.host)
 157     }
 158
 159     if (options.accountId) {
 160       this.whereAccountId(options.accountId)
 161     }
 162
 163     if (options.videoChannelId) {
 164       this.whereChannelId(options.videoChannelId)
 165     }
 166
 167     if (options.displayOnlyForFollower) {
 168       this.whereFollowerActorId(options.displayOnlyForFollower)
 169     }
 170
 171     if (options.hasFiles === true) {
 172       this.whereFileExists()
 173     }
 174
 175     if (exists(options.hasWebtorrentFiles)) {
 176       this.whereWebTorrentFileExists(options.hasWebtorrentFiles)
 177     }
 178
 179     if (exists(options.hasHLSFiles)) {
 180       this.whereHLSFileExists(options.hasHLSFiles)
 181     }
 182
 183     if (options.tagsOneOf) {
 184       this.whereTagsOneOf(options.tagsOneOf)
 185     }
 186
 187     if (options.tagsAllOf) {
 188       this.whereTagsAllOf(options.tagsAllOf)
 189     }
 190
 191     if (options.privacyOneOf) {
 192       this.wherePrivacyOneOf(options.privacyOneOf)
 193     } else {
 194       // Only list videos with the appropriate privacy
 195       this.wherePrivacyAvailable(options.user)
 196     }
 197
 198     if (options.uuids) {
 199       this.whereUUIDs(options.uuids)
 200     }
 201
 202     if (options.nsfw === true) {
 203       this.whereNSFW()
 204     } else if (options.nsfw === false) {
 205       this.whereSFW()
 206     }
 207
 208     if (options.isLive === true) {
 209       this.whereLive()
 210     } else if (options.isLive === false) {
 211       this.whereVOD()
 212     }
 213
 214     if (options.categoryOneOf) {
 215       this.whereCategoryOneOf(options.categoryOneOf)
 216     }
 217
 218     if (options.licenceOneOf) {
 219       this.whereLicenceOneOf(options.licenceOneOf)
 220     }
 221
 222     if (options.languageOneOf) {
 223       this.whereLanguageOneOf(options.languageOneOf)
 224     }
 225
 226     // We don't exclude results in this so if we do a count we don't need to add this complex clause
 227     if (options.isCount !== true) {
 228       if (options.trendingDays) {
 229         this.groupForTrending(options.trendingDays)
 230       } else if ([ 'best', 'hot' ].includes(options.trendingAlgorithm)) {
 231         this.groupForHotOrBest(options.trendingAlgorithm, options.user)
 232       }
 233     }
 234
 235     if (options.historyOfUser) {
 236       this.joinHistory(options.historyOfUser.id)
 237     }
 238
 239     if (options.startDate) {
 240       this.whereStartDate(options.startDate)
 241     }
 242
 243     if (options.endDate) {
 244       this.whereEndDate(options.endDate)
 245     }
 246
 247     if (options.originallyPublishedStartDate) {
 248       this.whereOriginallyPublishedStartDate(options.originallyPublishedStartDate)
 249     }
 250
 251     if (options.originallyPublishedEndDate) {
 252       this.whereOriginallyPublishedEndDate(options.originallyPublishedEndDate)
 253     }
 254
 255     if (options.durationMin) {
 256       this.whereDurationMin(options.durationMin)
 257     }
 258
 259     if (options.durationMax) {
 260       this.whereDurationMax(options.durationMax)
 261     }
 262
 263     this.whereSearch(options.search)
 264
 265     if (options.isCount === true) {
 266       this.setCountAttribute()
 267     } else {
 268       if (exists(options.sort)) {
 269         this.setSort(options.sort)
 270       }
 271
 272       if (exists(options.count)) {
 273         this.setLimit(options.count)
 274       }
 275
 276       if (exists(options.start)) {
 277         this.setOffset(options.start)
 278       }
 279     }
 280
 281     const cteString = this.cte.length !== 0
 282       ? `WITH ${this.cte.join(', ')} `
 283       : ''
 284
 285     this.query = cteString +
 286       'SELECT ' + this.attributes.join(', ') + ' ' +
 287       'FROM "video" ' + this.joins.join(' ') + ' ' +
 288       'WHERE ' + this.and.join(' AND ') + ' ' +
 289       this.group + ' ' +
 290       this.having + ' ' +
 291       this.sort + ' ' +
 292       this.limit + ' ' +
 293       this.offset
 294   }
 295
 296   private setCountAttribute () {
 297     this.attributes = [ 'COUNT(*) as "total"' ]
 298   }
 299
 300   private joinHistory (userId: number) {
 301     this.joins.push('INNER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId"')
 302
 303     this.and.push('"userVideoHistory"."userId" = :historyOfUser')
 304
 305     this.replacements.historyOfUser = userId
 306   }
 307
 308   private joinPlaylist (playlistId: number) {
 309     this.joins.push(
 310       'INNER JOIN "videoPlaylistElement" "video"."id" = "videoPlaylistElement"."videoId" ' +
 311       'AND "videoPlaylistElement"."videoPlaylistId" = :videoPlaylistId'
 312     )
 313
 314     this.replacements.videoPlaylistId = playlistId
 315   }
 316
 317   private whereStateAvailable () {
 318     this.and.push(
 319       `("video"."state" = ${VideoState.PUBLISHED} OR ` +
 320       `("video"."state" = ${VideoState.TO_TRANSCODE} AND "video"."waitTranscoding" IS false))`
 321     )
 322   }
 323
 324   private wherePrivacyAvailable (user?: MUserAccountId) {
 325     if (user) {
 326       this.and.push(
 327         `("video"."privacy" = ${VideoPrivacy.PUBLIC} OR "video"."privacy" = ${VideoPrivacy.INTERNAL})`
 328       )
 329     } else { // Or only public videos
 330       this.and.push(
 331         `"video"."privacy" = ${VideoPrivacy.PUBLIC}`
 332       )
 333     }
 334   }
 335
 336   private whereLocal (isLocal: boolean) {
 337     const isRemote = isLocal ? 'FALSE' : 'TRUE'
 338
 339     this.and.push('"video"."remote" IS ' + isRemote)
 340   }
 341
 342   private whereHost (host: string) {
 343     // Local instance
 344     if (host === WEBSERVER.HOST) {
 345       this.and.push('"accountActor"."serverId" IS NULL')
 346       return
 347     }
 348
 349     this.joins.push('INNER JOIN "server" ON "server"."id" = "accountActor"."serverId"')
 350
 351     this.and.push('"server"."host" = :host')
 352     this.replacements.host = host
 353   }
 354
 355   private whereAccountId (accountId: number) {
 356     this.and.push('"account"."id" = :accountId')
 357     this.replacements.accountId = accountId
 358   }
 359
 360   private whereChannelId (channelId: number) {
 361     this.and.push('"videoChannel"."id" = :videoChannelId')
 362     this.replacements.videoChannelId = channelId
 363   }
 364
 365   private whereFollowerActorId (options: { actorId: number, orLocalVideos: boolean }) {
 366     let query =
 367     '(' +
 368     '  EXISTS (' + // Videos shared by actors we follow
 369     '    SELECT 1 FROM "videoShare" ' +
 370     '    INNER JOIN "actorFollow" "actorFollowShare" ON "actorFollowShare"."targetActorId" = "videoShare"."actorId" ' +
 371     '    AND "actorFollowShare"."actorId" = :followerActorId AND "actorFollowShare"."state" = \'accepted\' ' +
 372     '    WHERE "videoShare"."videoId" = "video"."id"' +
 373     '  )' +
 374     '  OR' +
 375     '  EXISTS (' + // Videos published by channels or accounts we follow
 376     '    SELECT 1 from "actorFollow" ' +
 377     '    WHERE ("actorFollow"."targetActorId" = "account"."actorId" OR "actorFollow"."targetActorId" = "videoChannel"."actorId") ' +
 378     '    AND "actorFollow"."actorId" = :followerActorId ' +
 379     '    AND "actorFollow"."state" = \'accepted\'' +
 380     '  )'
 381
 382     if (options.orLocalVideos) {
 383       query += '  OR "video"."remote" IS FALSE'
 384     }
 385
 386     query += ')'
 387
 388     this.and.push(query)
 389     this.replacements.followerActorId = options.actorId
 390   }
 391
 392   private whereFileExists () {
 393     this.and.push(`(${this.buildWebTorrentFileExistsQuery(true)} OR ${this.buildHLSFileExistsQuery(true)})`)
 394   }
 395
 396   private whereWebTorrentFileExists (exists: boolean) {
 397     this.and.push(this.buildWebTorrentFileExistsQuery(exists))
 398   }
 399
 400   private whereHLSFileExists (exists: boolean) {
 401     this.and.push(this.buildHLSFileExistsQuery(exists))
 402   }
 403
 404   private buildWebTorrentFileExistsQuery (exists: boolean) {
 405     const prefix = exists ? '' : 'NOT '
 406
 407     return prefix + 'EXISTS (SELECT 1 FROM "videoFile" WHERE "videoFile"."videoId" = "video"."id")'
 408   }
 409
 410   private buildHLSFileExistsQuery (exists: boolean) {
 411     const prefix = exists ? '' : 'NOT '
 412
 413     return prefix + 'EXISTS (' +
 414     '  SELECT 1 FROM "videoStreamingPlaylist" ' +
 415     '  INNER JOIN "videoFile" ON "videoFile"."videoStreamingPlaylistId" = "videoStreamingPlaylist"."id" ' +
 416     '  WHERE "videoStreamingPlaylist"."videoId" = "video"."id"' +
 417     ')'
 418   }
 419
 420   private whereTagsOneOf (tagsOneOf: string[]) {
 421     const tagsOneOfLower = tagsOneOf.map(t => t.toLowerCase())
 422
 423     this.and.push(
 424       'EXISTS (' +
 425       '  SELECT 1 FROM "videoTag" ' +
 426       '  INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
 427       '  WHERE lower("tag"."name") IN (' + createSafeIn(this.sequelize, tagsOneOfLower) + ') ' +
 428       '  AND "video"."id" = "videoTag"."videoId"' +
 429       ')'
 430     )
 431   }
 432
 433   private whereTagsAllOf (tagsAllOf: string[]) {
 434     const tagsAllOfLower = tagsAllOf.map(t => t.toLowerCase())
 435
 436     this.and.push(
 437       'EXISTS (' +
 438       '  SELECT 1 FROM "videoTag" ' +
 439       '  INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
 440       '  WHERE lower("tag"."name") IN (' + createSafeIn(this.sequelize, tagsAllOfLower) + ') ' +
 441       '  AND "video"."id" = "videoTag"."videoId" ' +
 442       '  GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + tagsAllOfLower.length +
 443       ')'
 444     )
 445   }
 446
 447   private wherePrivacyOneOf (privacyOneOf: VideoPrivacy[]) {
 448     this.and.push('"video"."privacy" IN (:privacyOneOf)')
 449     this.replacements.privacyOneOf = privacyOneOf
 450   }
 451
 452   private whereUUIDs (uuids: string[]) {
 453     this.and.push('"video"."uuid" IN (' + createSafeIn(this.sequelize, uuids) + ')')
 454   }
 455
 456   private whereCategoryOneOf (categoryOneOf: number[]) {
 457     this.and.push('"video"."category" IN (:categoryOneOf)')
 458     this.replacements.categoryOneOf = categoryOneOf
 459   }
 460
 461   private whereLicenceOneOf (licenceOneOf: number[]) {
 462     this.and.push('"video"."licence" IN (:licenceOneOf)')
 463     this.replacements.licenceOneOf = licenceOneOf
 464   }
 465
 466   private whereLanguageOneOf (languageOneOf: string[]) {
 467     const languages = languageOneOf.filter(l => l && l !== '_unknown')
 468     const languagesQueryParts: string[] = []
 469
 470     if (languages.length !== 0) {
 471       languagesQueryParts.push('"video"."language" IN (:languageOneOf)')
 472       this.replacements.languageOneOf = languages
 473
 474       languagesQueryParts.push(
 475         'EXISTS (' +
 476         '  SELECT 1 FROM "videoCaption" WHERE "videoCaption"."language" ' +
 477         '  IN (' + createSafeIn(this.sequelize, languages) + ') AND ' +
 478         '  "videoCaption"."videoId" = "video"."id"' +
 479         ')'
 480       )
 481     }
 482
 483     if (languageOneOf.includes('_unknown')) {
 484       languagesQueryParts.push('"video"."language" IS NULL')
 485     }
 486
 487     if (languagesQueryParts.length !== 0) {
 488       this.and.push('(' + languagesQueryParts.join(' OR ') + ')')
 489     }
 490   }
 491
 492   private whereNSFW () {
 493     this.and.push('"video"."nsfw" IS TRUE')
 494   }
 495
 496   private whereSFW () {
 497     this.and.push('"video"."nsfw" IS FALSE')
 498   }
 499
 500   private whereLive () {
 501     this.and.push('"video"."isLive" IS TRUE')
 502   }
 503
 504   private whereVOD () {
 505     this.and.push('"video"."isLive" IS FALSE')
 506   }
 507
 508   private whereNotBlocked (serverAccountId: number, user?: MUserAccountId) {
 509     const blockerIds = [ serverAccountId ]
 510     if (user) blockerIds.push(user.Account.id)
 511
 512     const inClause = createSafeIn(this.sequelize, blockerIds)
 513
 514     this.and.push(
 515       'NOT EXISTS (' +
 516       '  SELECT 1 FROM "accountBlocklist" ' +
 517       '  WHERE "accountBlocklist"."accountId" IN (' + inClause + ') ' +
 518       '  AND "accountBlocklist"."targetAccountId" = "account"."id" ' +
 519       ')' +
 520       'AND NOT EXISTS (' +
 521       '  SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN (' + inClause + ') ' +
 522       '  AND "serverBlocklist"."targetServerId" = "accountActor"."serverId"' +
 523       ')'
 524     )
 525   }
 526
 527   private whereSearch (search?: string) {
 528     if (!search) {
 529       this.attributes.push('0 as similarity')
 530       return
 531     }
 532
 533     const escapedSearch = this.sequelize.escape(search)
 534     const escapedLikeSearch = this.sequelize.escape('%' + search + '%')
 535
 536     this.cte.push(
 537       '"trigramSearch" AS (' +
 538       '  SELECT "video"."id", ' +
 539       `  similarity(lower(immutable_unaccent("video"."name")), lower(immutable_unaccent(${escapedSearch}))) as similarity ` +
 540       '  FROM "video" ' +
 541       '  WHERE lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' +
 542       '        lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' +
 543       ')'
 544     )
 545
 546     this.joins.push('LEFT JOIN "trigramSearch" ON "video"."id" = "trigramSearch"."id"')
 547
 548     let base = '(' +
 549     '  "trigramSearch"."id" IS NOT NULL OR ' +
 550     '  EXISTS (' +
 551     '    SELECT 1 FROM "videoTag" ' +
 552     '    INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
 553     `    WHERE lower("tag"."name") = lower(${escapedSearch}) ` +
 554     '    AND "video"."id" = "videoTag"."videoId"' +
 555     '  )'
 556
 557     if (validator.isUUID(search)) {
 558       base += ` OR "video"."uuid" = ${escapedSearch}`
 559     }
 560
 561     base += ')'
 562
 563     this.and.push(base)
 564     this.attributes.push(`COALESCE("trigramSearch"."similarity", 0) as similarity`)
 565   }
 566
 567   private whereNotBlacklisted () {
 568     this.and.push('"video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")')
 569   }
 570
 571   private whereStartDate (startDate: string) {
 572     this.and.push('"video"."publishedAt" >= :startDate')
 573     this.replacements.startDate = startDate
 574   }
 575
 576   private whereEndDate (endDate: string) {
 577     this.and.push('"video"."publishedAt" <= :endDate')
 578     this.replacements.endDate = endDate
 579   }
 580
 581   private whereOriginallyPublishedStartDate (startDate: string) {
 582     this.and.push('"video"."originallyPublishedAt" >= :originallyPublishedStartDate')
 583     this.replacements.originallyPublishedStartDate = startDate
 584   }
 585
 586   private whereOriginallyPublishedEndDate (endDate: string) {
 587     this.and.push('"video"."originallyPublishedAt" <= :originallyPublishedEndDate')
 588     this.replacements.originallyPublishedEndDate = endDate
 589   }
 590
 591   private whereDurationMin (durationMin: number) {
 592     this.and.push('"video"."duration" >= :durationMin')
 593     this.replacements.durationMin = durationMin
 594   }
 595
 596   private whereDurationMax (durationMax: number) {
 597     this.and.push('"video"."duration" <= :durationMax')
 598     this.replacements.durationMax = durationMax
 599   }
 600
 601   private groupForTrending (trendingDays: number) {
 602     const viewsGteDate = new Date(new Date().getTime() - (24 * 3600 * 1000) * trendingDays)
 603
 604     this.joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate')
 605     this.replacements.viewsGteDate = viewsGteDate
 606
 607     this.attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "score"')
 608
 609     this.group = 'GROUP BY "video"."id"'
 610   }
 611
 612   private groupForHotOrBest (trendingAlgorithm: string, user?: MUserAccountId) {
 613     /**
 614      * "Hotness" is a measure based on absolute view/comment/like/dislike numbers,
 615      * with fixed weights only applied to their log values.
 616      *
 617      * This algorithm gives little chance for an old video to have a good score,
 618      * for which recent spikes in interactions could be a sign of "hotness" and
 619      * justify a better score. However there are multiple ways to achieve that
 620      * goal, which is left for later. Yes, this is a TODO :)
 621      *
 622      * notes:
 623      *  - weights and base score are in number of half-days.
 624      *  - all comments are counted, regardless of being written by the video author or not
 625      * see https://github.com/reddit-archive/reddit/blob/master/r2/r2/lib/db/_sorts.pyx#L47-L58
 626      *  - we have less interactions than on reddit, so multiply weights by an arbitrary factor
 627      */
 628     const weights = {
 629       like: 3 * 50,
 630       dislike: -3 * 50,
 631       view: Math.floor((1 / 3) * 50),
 632       comment: 2 * 50, // a comment takes more time than a like to do, but can be done multiple times
 633       history: -2 * 50
 634     }
 635
 636     this.joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"')
 637
 638     let attribute =
 639       `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+)
 640       `+ LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-)
 641       `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+)
 642       `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id"))) * ${weights.comment} ` + // comments (+)
 643       '+ (SELECT (EXTRACT(epoch FROM "video"."publishedAt") - 1446156582) / 47000) ' // base score (in number of half-days)
 644
 645     if (trendingAlgorithm === 'best' && user) {
 646       this.joins.push(
 647         'LEFT JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :bestUser'
 648       )
 649       this.replacements.bestUser = user.id
 650
 651       attribute += `+ POWER(COUNT(DISTINCT "userVideoHistory"."id"), 2.0) * ${weights.history} `
 652     }
 653
 654     attribute += 'AS "score"'
 655     this.attributes.push(attribute)
 656
 657     this.group = 'GROUP BY "video"."id"'
 658   }
 659
 660   private setSort (sort: string) {
 661     if (sort === '-originallyPublishedAt' || sort === 'originallyPublishedAt') {
 662       this.attributes.push('COALESCE("video"."originallyPublishedAt", "video"."publishedAt") AS "publishedAtForOrder"')
 663     }
 664
 665     this.sort = this.buildOrder(sort)
 666   }
 667
 668   private buildOrder (value: string) {
 669     const { direction, field } = buildSortDirectionAndField(value)
 670     if (field.match(/^[a-zA-Z."]+$/) === null) throw new Error('Invalid sort column ' + field)
 671
 672     if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()'
 673
 674     if ([ 'trending', 'hot', 'best' ].includes(field.toLowerCase())) { // Sort by aggregation
 675       return `ORDER BY "score" ${direction}, "video"."views" ${direction}`
 676     }
 677
 678     let firstSort: string
 679
 680     if (field.toLowerCase() === 'match') { // Search
 681       firstSort = '"similarity"'
 682     } else if (field === 'originallyPublishedAt') {
 683       firstSort = '"publishedAtForOrder"'
 684     } else if (field.includes('.')) {
 685       firstSort = field
 686     } else {
 687       firstSort = `"video"."${field}"`
 688     }
 689
 690     return `ORDER BY ${firstSort} ${direction}, "video"."id" ASC`
 691   }
 692
 693   private setLimit (countArg: number) {
 694     const count = forceNumber(countArg)
 695     this.limit = `LIMIT ${count}`
 696   }
 697
 698   private setOffset (startArg: number) {
 699     const start = forceNumber(startArg)
 700     this.offset = `OFFSET ${start}`
 701   }
 702 }