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