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

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