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

   1
   2 import { MUserId } from '@server/types/models'
   3 import { Sequelize } from 'sequelize'
   4 import { AbstractVideosQueryBuilder } from './abstract-videos-query-builder'
   5 import { buildVideosFromRows } from './video-model-builder'
   6 import { BuildVideosListQueryOptions, VideosIdListQueryBuilder } from './videos-id-list-query-builder'
   7
   8 export class VideosModelListQueryBuilder extends AbstractVideosQueryBuilder {
   9   private attributes: { [key: string]: string }
  10
  11   private joins: string[] = []
  12
  13   private innerQuery: string
  14   private innerSort: string
  15
  16   constructor (protected readonly sequelize: Sequelize) {
  17     super()
  18   }
  19
  20   queryVideos (options: BuildVideosListQueryOptions) {
  21     this.buildInnerQuery(options)
  22     this.buildListQueryFromIdsQuery(options)
  23
  24     return this.runQuery(true).then(rows => buildVideosFromRows(rows))
  25   }
  26
  27   private buildInnerQuery (options: BuildVideosListQueryOptions) {
  28     const idsQueryBuilder = new VideosIdListQueryBuilder(this.sequelize)
  29     const { query, sort, replacements } = idsQueryBuilder.getIdsListQueryAndSort(options)
  30
  31     this.replacements = replacements
  32     this.innerQuery = query
  33     this.innerSort = sort
  34   }
  35
  36   private buildListQueryFromIdsQuery (options: BuildVideosListQueryOptions) {
  37     this.attributes = {
  38       '"video".*': ''
  39     }
  40
  41     this.joins = [ 'INNER JOIN "video" ON "tmp"."id" = "video"."id"' ]
  42
  43     this.includeChannels()
  44     this.includeAccounts()
  45     this.includeThumbnails()
  46
  47     if (options.withFiles) {
  48       this.includeFiles()
  49     }
  50
  51     if (options.user) {
  52       this.includeUserHistory(options.user)
  53     }
  54
  55     if (options.videoPlaylistId) {
  56       this.includePlaylist(options.videoPlaylistId)
  57     }
  58
  59     const select = this.buildSelect()
  60
  61     this.query = `${select} FROM (${this.innerQuery}) AS "tmp" ${this.joins.join(' ')} ${this.innerSort}`
  62   }
  63
  64   private includeChannels () {
  65     this.attributes = {
  66       ...this.attributes,
  67
  68       '"VideoChannel"."id"': '"VideoChannel.id"',
  69       '"VideoChannel"."name"': '"VideoChannel.name"',
  70       '"VideoChannel"."description"': '"VideoChannel.description"',
  71       '"VideoChannel"."actorId"': '"VideoChannel.actorId"',
  72       '"VideoChannel->Actor"."id"': '"VideoChannel.Actor.id"',
  73       '"VideoChannel->Actor"."preferredUsername"': '"VideoChannel.Actor.preferredUsername"',
  74       '"VideoChannel->Actor"."url"': '"VideoChannel.Actor.url"',
  75       '"VideoChannel->Actor"."serverId"': '"VideoChannel.Actor.serverId"',
  76       '"VideoChannel->Actor"."avatarId"': '"VideoChannel.Actor.avatarId"',
  77       '"VideoChannel->Actor->Server"."id"': '"VideoChannel.Actor.Server.id"',
  78       '"VideoChannel->Actor->Server"."host"': '"VideoChannel.Actor.Server.host"',
  79       '"VideoChannel->Actor->Avatar"."id"': '"VideoChannel.Actor.Avatar.id"',
  80       '"VideoChannel->Actor->Avatar"."filename"': '"VideoChannel.Actor.Avatar.filename"',
  81       '"VideoChannel->Actor->Avatar"."fileUrl"': '"VideoChannel.Actor.Avatar.fileUrl"',
  82       '"VideoChannel->Actor->Avatar"."onDisk"': '"VideoChannel.Actor.Avatar.onDisk"',
  83       '"VideoChannel->Actor->Avatar"."createdAt"': '"VideoChannel.Actor.Avatar.createdAt"',
  84       '"VideoChannel->Actor->Avatar"."updatedAt"': '"VideoChannel.Actor.Avatar.updatedAt"'
  85     }
  86
  87     this.joins = this.joins.concat([
  88       'INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id"',
  89       'INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id"',
  90
  91       'LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id"',
  92       'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Actor->Avatar" ' +
  93         'ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id"'
  94     ])
  95   }
  96
  97   private includeAccounts () {
  98     this.attributes = {
  99       ...this.attributes,
 100
 101       '"VideoChannel->Account"."id"': '"VideoChannel.Account.id"',
 102       '"VideoChannel->Account"."name"': '"VideoChannel.Account.name"',
 103       '"VideoChannel->Account->Actor"."id"': '"VideoChannel.Account.Actor.id"',
 104       '"VideoChannel->Account->Actor"."preferredUsername"': '"VideoChannel.Account.Actor.preferredUsername"',
 105       '"VideoChannel->Account->Actor"."url"': '"VideoChannel.Account.Actor.url"',
 106       '"VideoChannel->Account->Actor"."serverId"': '"VideoChannel.Account.Actor.serverId"',
 107       '"VideoChannel->Account->Actor"."avatarId"': '"VideoChannel.Account.Actor.avatarId"',
 108       '"VideoChannel->Account->Actor->Server"."id"': '"VideoChannel.Account.Actor.Server.id"',
 109       '"VideoChannel->Account->Actor->Server"."host"': '"VideoChannel.Account.Actor.Server.host"',
 110       '"VideoChannel->Account->Actor->Avatar"."id"': '"VideoChannel.Account.Actor.Avatar.id"',
 111       '"VideoChannel->Account->Actor->Avatar"."filename"': '"VideoChannel.Account.Actor.Avatar.filename"',
 112       '"VideoChannel->Account->Actor->Avatar"."fileUrl"': '"VideoChannel.Account.Actor.Avatar.fileUrl"',
 113       '"VideoChannel->Account->Actor->Avatar"."onDisk"': '"VideoChannel.Account.Actor.Avatar.onDisk"',
 114       '"VideoChannel->Account->Actor->Avatar"."createdAt"': '"VideoChannel.Account.Actor.Avatar.createdAt"',
 115       '"VideoChannel->Account->Actor->Avatar"."updatedAt"': '"VideoChannel.Account.Actor.Avatar.updatedAt"'
 116     }
 117
 118     this.joins = this.joins.concat([
 119       'INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"',
 120       'INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id"',
 121
 122       'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' +
 123         'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"',
 124
 125       'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatar" ' +
 126         'ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id"'
 127     ])
 128   }
 129
 130   private includeThumbnails () {
 131     this.attributes = {
 132       ...this.attributes,
 133
 134       '"Thumbnails"."id"': '"Thumbnails.id"',
 135       '"Thumbnails"."type"': '"Thumbnails.type"',
 136       '"Thumbnails"."filename"': '"Thumbnails.filename"'
 137     }
 138
 139     this.joins.push('LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId"')
 140   }
 141
 142   private includeFiles () {
 143     this.attributes = {
 144       ...this.attributes,
 145
 146       '"VideoFiles"."id"': '"VideoFiles.id"',
 147       '"VideoFiles"."createdAt"': '"VideoFiles.createdAt"',
 148       '"VideoFiles"."updatedAt"': '"VideoFiles.updatedAt"',
 149       '"VideoFiles"."resolution"': '"VideoFiles.resolution"',
 150       '"VideoFiles"."size"': '"VideoFiles.size"',
 151       '"VideoFiles"."extname"': '"VideoFiles.extname"',
 152       '"VideoFiles"."filename"': '"VideoFiles.filename"',
 153       '"VideoFiles"."fileUrl"': '"VideoFiles.fileUrl"',
 154       '"VideoFiles"."torrentFilename"': '"VideoFiles.torrentFilename"',
 155       '"VideoFiles"."torrentUrl"': '"VideoFiles.torrentUrl"',
 156       '"VideoFiles"."infoHash"': '"VideoFiles.infoHash"',
 157       '"VideoFiles"."fps"': '"VideoFiles.fps"',
 158       '"VideoFiles"."videoId"': '"VideoFiles.videoId"',
 159
 160       '"VideoStreamingPlaylists"."id"': '"VideoStreamingPlaylists.id"',
 161       '"VideoStreamingPlaylists"."playlistUrl"': '"VideoStreamingPlaylists.playlistUrl"',
 162       '"VideoStreamingPlaylists"."type"': '"VideoStreamingPlaylists.type"',
 163       '"VideoStreamingPlaylists->VideoFiles"."id"': '"VideoStreamingPlaylists.VideoFiles.id"',
 164       '"VideoStreamingPlaylists->VideoFiles"."createdAt"': '"VideoStreamingPlaylists.VideoFiles.createdAt"',
 165       '"VideoStreamingPlaylists->VideoFiles"."updatedAt"': '"VideoStreamingPlaylists.VideoFiles.updatedAt"',
 166       '"VideoStreamingPlaylists->VideoFiles"."resolution"': '"VideoStreamingPlaylists.VideoFiles.resolution"',
 167       '"VideoStreamingPlaylists->VideoFiles"."size"': '"VideoStreamingPlaylists.VideoFiles.size"',
 168       '"VideoStreamingPlaylists->VideoFiles"."extname"': '"VideoStreamingPlaylists.VideoFiles.extname"',
 169       '"VideoStreamingPlaylists->VideoFiles"."filename"': '"VideoStreamingPlaylists.VideoFiles.filename"',
 170       '"VideoStreamingPlaylists->VideoFiles"."fileUrl"': '"VideoStreamingPlaylists.VideoFiles.fileUrl"',
 171       '"VideoStreamingPlaylists->VideoFiles"."torrentFilename"': '"VideoStreamingPlaylists.VideoFiles.torrentFilename"',
 172       '"VideoStreamingPlaylists->VideoFiles"."torrentUrl"': '"VideoStreamingPlaylists.VideoFiles.torrentUrl"',
 173       '"VideoStreamingPlaylists->VideoFiles"."infoHash"': '"VideoStreamingPlaylists.VideoFiles.infoHash"',
 174       '"VideoStreamingPlaylists->VideoFiles"."fps"': '"VideoStreamingPlaylists.VideoFiles.fps"',
 175       '"VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId"': '"VideoStreamingPlaylists.VideoFiles.videoStreamingPlaylistId"',
 176       '"VideoStreamingPlaylists->VideoFiles"."videoId"': '"VideoStreamingPlaylists.VideoFiles.videoId"'
 177     }
 178
 179     this.joins = this.joins.concat([
 180       'LEFT JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"',
 181
 182       'LEFT JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"',
 183
 184       'LEFT JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' +
 185         'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"'
 186     ])
 187   }
 188
 189   private includeUserHistory (user: MUserId) {
 190     this.attributes = {
 191       ...this.attributes,
 192
 193       '"userVideoHistory"."id"': '"userVideoHistory.id"',
 194       '"userVideoHistory"."currentTime"': '"userVideoHistory.currentTime"'
 195     }
 196
 197     this.joins.push(
 198       'LEFT OUTER JOIN "userVideoHistory" ' +
 199       'ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :userVideoHistoryId'
 200     )
 201
 202     this.replacements.userVideoHistoryId = user.id
 203   }
 204
 205   private includePlaylist (playlistId: number) {
 206     this.attributes = {
 207       ...this.attributes,
 208
 209       '"VideoPlaylistElement"."createdAt"': '"VideoPlaylistElement.createdAt"',
 210       '"VideoPlaylistElement"."updatedAt"': '"VideoPlaylistElement.updatedAt"',
 211       '"VideoPlaylistElement"."url"': '"VideoPlaylistElement.url"',
 212       '"VideoPlaylistElement"."position"': '"VideoPlaylistElement.position"',
 213       '"VideoPlaylistElement"."startTimestamp"': '"VideoPlaylistElement.startTimestamp"',
 214       '"VideoPlaylistElement"."stopTimestamp"': '"VideoPlaylistElement.stopTimestamp"',
 215       '"VideoPlaylistElement"."videoPlaylistId"': '"VideoPlaylistElement.videoPlaylistId"'
 216     }
 217
 218     this.joins.push(
 219       'INNER JOIN "videoPlaylistElement" as "VideoPlaylistElement" ON "videoPlaylistElement"."videoId" = "video"."id" ' +
 220       'AND "VideoPlaylistElement"."videoPlaylistId" = :videoPlaylistId'
 221     )
 222
 223     this.replacements.videoPlaylistId = playlistId
 224   }
 225
 226   private buildSelect () {
 227     return 'SELECT ' + Object.keys(this.attributes).map(key => {
 228       const value = this.attributes[key]
 229       if (value) return `${key} AS ${value}`
 230
 231       return key
 232     }).join(', ')
 233   }
 234 }