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