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