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