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