aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/video/sql
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2021-06-10 08:53:32 +0200
committerChocobozzz <me@florianbigard.com>2021-06-10 09:22:58 +0200
commite5dbd5084e7ae91ce118c0bccd5b84c47b88c55f (patch)
treee7ae22528a0cf5b181f7cefbf867e641e9cacef9 /server/models/video/sql
parentff0ea0cd8e3b0ecad445672deb75b193babeddc2 (diff)
downloadPeerTube-e5dbd5084e7ae91ce118c0bccd5b84c47b88c55f.tar.gz
PeerTube-e5dbd5084e7ae91ce118c0bccd5b84c47b88c55f.tar.zst
PeerTube-e5dbd5084e7ae91ce118c0bccd5b84c47b88c55f.zip
Refactor video query builder
Diffstat (limited to 'server/models/video/sql')
-rw-r--r--server/models/video/sql/abstract-videos-query-builder.ts15
-rw-r--r--server/models/video/sql/video-model-builder.ts162
-rw-r--r--server/models/video/sql/videos-id-list-query-builder.ts609
-rw-r--r--server/models/video/sql/videos-model-list-query-builder.ts234
4 files changed, 1020 insertions, 0 deletions
diff --git a/server/models/video/sql/abstract-videos-query-builder.ts b/server/models/video/sql/abstract-videos-query-builder.ts
new file mode 100644
index 000000000..597a02af7
--- /dev/null
+++ b/server/models/video/sql/abstract-videos-query-builder.ts
@@ -0,0 +1,15 @@
1import { logger } from '@server/helpers/logger'
2import { Sequelize, QueryTypes } from 'sequelize'
3
4export class AbstractVideosQueryBuilder {
5 protected sequelize: Sequelize
6
7 protected query: string
8 protected replacements: any = {}
9
10 protected runQuery (nest?: boolean) {
11 logger.info('Running video query.', { query: this.query, replacements: this.replacements })
12
13 return this.sequelize.query<any>(this.query, { replacements: this.replacements, type: QueryTypes.SELECT, nest })
14 }
15}
diff --git a/server/models/video/sql/video-model-builder.ts b/server/models/video/sql/video-model-builder.ts
new file mode 100644
index 000000000..c428312fe
--- /dev/null
+++ b/server/models/video/sql/video-model-builder.ts
@@ -0,0 +1,162 @@
1import { pick } from 'lodash'
2import { AccountModel } from '@server/models/account/account'
3import { ActorModel } from '@server/models/actor/actor'
4import { ActorImageModel } from '@server/models/actor/actor-image'
5import { ServerModel } from '@server/models/server/server'
6import { UserVideoHistoryModel } from '@server/models/user/user-video-history'
7import { ThumbnailModel } from '../thumbnail'
8import { VideoModel } from '../video'
9import { VideoChannelModel } from '../video-channel'
10import { VideoFileModel } from '../video-file'
11import { VideoStreamingPlaylistModel } from '../video-streaming-playlist'
12
13function buildVideosFromRows (rows: any[]) {
14 const videosMemo: { [ id: number ]: VideoModel } = {}
15 const videoStreamingPlaylistMemo: { [ id: number ]: VideoStreamingPlaylistModel } = {}
16
17 const thumbnailsDone = new Set<number>()
18 const historyDone = new Set<number>()
19 const videoFilesDone = new Set<number>()
20
21 const videos: VideoModel[] = []
22
23 const avatarKeys = [ 'id', 'filename', 'fileUrl', 'onDisk', 'createdAt', 'updatedAt' ]
24 const actorKeys = [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ]
25 const serverKeys = [ 'id', 'host' ]
26 const videoFileKeys = [
27 'id',
28 'createdAt',
29 'updatedAt',
30 'resolution',
31 'size',
32 'extname',
33 'filename',
34 'fileUrl',
35 'torrentFilename',
36 'torrentUrl',
37 'infoHash',
38 'fps',
39 'videoId',
40 'videoStreamingPlaylistId'
41 ]
42 const videoStreamingPlaylistKeys = [ 'id', 'type', 'playlistUrl' ]
43 const videoKeys = [
44 'id',
45 'uuid',
46 'name',
47 'category',
48 'licence',
49 'language',
50 'privacy',
51 'nsfw',
52 'description',
53 'support',
54 'duration',
55 'views',
56 'likes',
57 'dislikes',
58 'remote',
59 'isLive',
60 'url',
61 'commentsEnabled',
62 'downloadEnabled',
63 'waitTranscoding',
64 'state',
65 'publishedAt',
66 'originallyPublishedAt',
67 'channelId',
68 'createdAt',
69 'updatedAt'
70 ]
71 const buildOpts = { raw: true }
72
73 function buildActor (rowActor: any) {
74 const avatarModel = rowActor.Avatar.id !== null
75 ? new ActorImageModel(pick(rowActor.Avatar, avatarKeys), buildOpts)
76 : null
77
78 const serverModel = rowActor.Server.id !== null
79 ? new ServerModel(pick(rowActor.Server, serverKeys), buildOpts)
80 : null
81
82 const actorModel = new ActorModel(pick(rowActor, actorKeys), buildOpts)
83 actorModel.Avatar = avatarModel
84 actorModel.Server = serverModel
85
86 return actorModel
87 }
88
89 for (const row of rows) {
90 if (!videosMemo[row.id]) {
91 // Build Channel
92 const channel = row.VideoChannel
93 const channelModel = new VideoChannelModel(pick(channel, [ 'id', 'name', 'description', 'actorId' ]), buildOpts)
94 channelModel.Actor = buildActor(channel.Actor)
95
96 const account = row.VideoChannel.Account
97 const accountModel = new AccountModel(pick(account, [ 'id', 'name' ]), buildOpts)
98 accountModel.Actor = buildActor(account.Actor)
99
100 channelModel.Account = accountModel
101
102 const videoModel = new VideoModel(pick(row, videoKeys), buildOpts)
103 videoModel.VideoChannel = channelModel
104
105 videoModel.UserVideoHistories = []
106 videoModel.Thumbnails = []
107 videoModel.VideoFiles = []
108 videoModel.VideoStreamingPlaylists = []
109
110 videosMemo[row.id] = videoModel
111 // Don't take object value to have a sorted array
112 videos.push(videoModel)
113 }
114
115 const videoModel = videosMemo[row.id]
116
117 if (row.userVideoHistory?.id && !historyDone.has(row.userVideoHistory.id)) {
118 const historyModel = new UserVideoHistoryModel(pick(row.userVideoHistory, [ 'id', 'currentTime' ]), buildOpts)
119 videoModel.UserVideoHistories.push(historyModel)
120
121 historyDone.add(row.userVideoHistory.id)
122 }
123
124 if (row.Thumbnails?.id && !thumbnailsDone.has(row.Thumbnails.id)) {
125 const thumbnailModel = new ThumbnailModel(pick(row.Thumbnails, [ 'id', 'type', 'filename' ]), buildOpts)
126 videoModel.Thumbnails.push(thumbnailModel)
127
128 thumbnailsDone.add(row.Thumbnails.id)
129 }
130
131 if (row.VideoFiles?.id && !videoFilesDone.has(row.VideoFiles.id)) {
132 const videoFileModel = new VideoFileModel(pick(row.VideoFiles, videoFileKeys), buildOpts)
133 videoModel.VideoFiles.push(videoFileModel)
134
135 videoFilesDone.add(row.VideoFiles.id)
136 }
137
138 if (row.VideoStreamingPlaylists?.id && !videoStreamingPlaylistMemo[row.VideoStreamingPlaylists.id]) {
139 const streamingPlaylist = new VideoStreamingPlaylistModel(pick(row.VideoStreamingPlaylists, videoStreamingPlaylistKeys), buildOpts)
140 streamingPlaylist.VideoFiles = []
141
142 videoModel.VideoStreamingPlaylists.push(streamingPlaylist)
143
144 videoStreamingPlaylistMemo[streamingPlaylist.id] = streamingPlaylist
145 }
146
147 if (row.VideoStreamingPlaylists?.VideoFiles?.id && !videoFilesDone.has(row.VideoStreamingPlaylists.VideoFiles.id)) {
148 const streamingPlaylist = videoStreamingPlaylistMemo[row.VideoStreamingPlaylists.id]
149
150 const videoFileModel = new VideoFileModel(pick(row.VideoStreamingPlaylists.VideoFiles, videoFileKeys), buildOpts)
151 streamingPlaylist.VideoFiles.push(videoFileModel)
152
153 videoFilesDone.add(row.VideoStreamingPlaylists.VideoFiles.id)
154 }
155 }
156
157 return videos
158}
159
160export {
161 buildVideosFromRows
162}
diff --git a/server/models/video/sql/videos-id-list-query-builder.ts b/server/models/video/sql/videos-id-list-query-builder.ts
new file mode 100644
index 000000000..7bb942ea4
--- /dev/null
+++ b/server/models/video/sql/videos-id-list-query-builder.ts
@@ -0,0 +1,609 @@
1import { Sequelize } from 'sequelize'
2import validator from 'validator'
3import { exists } from '@server/helpers/custom-validators/misc'
4import { buildDirectionAndField, createSafeIn } from '@server/models/utils'
5import { MUserAccountId, MUserId } from '@server/types/models'
6import { VideoFilter, VideoPrivacy, VideoState } from '@shared/models'
7import { AbstractVideosQueryBuilder } from './abstract-videos-query-builder'
8
9export 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
59export 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}
diff --git a/server/models/video/sql/videos-model-list-query-builder.ts b/server/models/video/sql/videos-model-list-query-builder.ts
new file mode 100644
index 000000000..4ba9dd878
--- /dev/null
+++ b/server/models/video/sql/videos-model-list-query-builder.ts
@@ -0,0 +1,234 @@
1
2import { MUserId } from '@server/types/models'
3import { Sequelize } from 'sequelize'
4import { AbstractVideosQueryBuilder } from './abstract-videos-query-builder'
5import { buildVideosFromRows } from './video-model-builder'
6import { BuildVideosListQueryOptions, VideosIdListQueryBuilder } from './videos-id-list-query-builder'
7
8export 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}