]>
Commit | Line | Data |
---|---|---|
16c016e8 | 1 | import { Sequelize } from 'sequelize/types' |
5f3e2425 | 2 | import validator from 'validator' |
fab67463 | 3 | import { exists } from '@server/helpers/custom-validators/misc' |
16c016e8 C |
4 | import { buildDirectionAndField, createSafeIn } from '@server/models/utils' |
5 | import { MUserAccountId, MUserId } from '@server/types/models' | |
6 | import { VideoFilter, VideoPrivacy, VideoState } from '@shared/models' | |
5f3e2425 C |
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 | ||
1fd61899 | 19 | nsfw?: boolean |
5f3e2425 | 20 | filter?: VideoFilter |
1fd61899 C |
21 | isLive?: boolean |
22 | ||
5f3e2425 | 23 | categoryOneOf?: number[] |
5f3e2425 C |
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 | ||
3d4e112d | 36 | trendingAlgorithm?: string // best, hot, or any other algorithm implemented |
5f3e2425 | 37 | trendingDays?: number |
5bcbcbe3 | 38 | |
5f3e2425 C |
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 | ||
16c016e8 | 58 | function buildListQuery (sequelize: Sequelize, options: BuildVideosQueryOptions) { |
5f3e2425 | 59 | const and: string[] = [] |
5f3e2425 C |
60 | const joins: string[] = [] |
61 | const replacements: any = {} | |
6b842050 | 62 | const cte: string[] = [] |
5f3e2425 C |
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"' + | |
6b842050 | 71 | 'INNER JOIN "actor" "accountActor" ON "account"."actorId" = "accountActor"."id"' |
5f3e2425 C |
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 | ||
16c016e8 | 80 | const inClause = createSafeIn(sequelize, blockerIds) |
5f3e2425 C |
81 | |
82 | and.push( | |
6b842050 C |
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 | ')' | |
5f3e2425 | 92 | ) |
5f3e2425 C |
93 | } |
94 | ||
95 | // Only list public/published videos | |
0aa52e17 | 96 | if (!options.filter || (options.filter !== 'all-local' && options.filter !== 'all')) { |
5f3e2425 C |
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" ' + | |
f046e2fa C |
142 | ' AND "actorFollowShare"."actorId" = :followerActorId AND "actorFollowShare"."state" = \'accepted\' ' + |
143 | ' WHERE "videoShare"."videoId" = "video"."id"' + | |
5f3e2425 C |
144 | ' )' + |
145 | ' OR' + | |
146 | ' EXISTS (' + | |
147 | ' SELECT 1 from "actorFollow" ' + | |
f046e2fa C |
148 | ' WHERE "actorFollow"."targetActorId" = "videoChannel"."actorId" AND "actorFollow"."actorId" = :followerActorId ' + |
149 | ' AND "actorFollow"."state" = \'accepted\'' + | |
5f3e2425 C |
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) { | |
97816649 C |
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 | ) | |
5f3e2425 C |
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" ' + | |
16c016e8 | 182 | ' WHERE lower("tag"."name") IN (' + createSafeIn(sequelize, tagsOneOfLower) + ') ' + |
5f3e2425 C |
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" ' + | |
16c016e8 | 195 | ' WHERE lower("tag"."name") IN (' + createSafeIn(sequelize, tagsAllOfLower) + ') ' + |
5f3e2425 C |
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') | |
1fd61899 C |
204 | } else if (options.nsfw === false) { |
205 | and.push('"video"."nsfw" IS FALSE') | |
5f3e2425 C |
206 | } |
207 | ||
1fd61899 C |
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') | |
5f3e2425 C |
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) { | |
14cbb9a6 C |
225 | const languages = options.languageOneOf.filter(l => l && l !== '_unknown') |
226 | const languagesQueryParts: string[] = [] | |
227 | ||
228 | if (languages.length !== 0) { | |
8f31261f | 229 | languagesQueryParts.push('"video"."language" IN (:languageOneOf)') |
14cbb9a6 C |
230 | replacements.languageOneOf = languages |
231 | ||
232 | languagesQueryParts.push( | |
8f31261f C |
233 | 'EXISTS (' + |
234 | ' SELECT 1 FROM "videoCaption" WHERE "videoCaption"."language" ' + | |
16c016e8 | 235 | ' IN (' + createSafeIn(sequelize, languages) + ') AND ' + |
8f31261f | 236 | ' "videoCaption"."videoId" = "video"."id"' + |
14cbb9a6 C |
237 | ')' |
238 | ) | |
239 | } | |
5f3e2425 C |
240 | |
241 | if (options.languageOneOf.includes('_unknown')) { | |
14cbb9a6 | 242 | languagesQueryParts.push('"video"."language" IS NULL') |
5f3e2425 C |
243 | } |
244 | ||
8f31261f C |
245 | if (languagesQueryParts.length !== 0) { |
246 | and.push('(' + languagesQueryParts.join(' OR ') + ')') | |
247 | } | |
5f3e2425 C |
248 | } |
249 | ||
5bcbcbe3 | 250 | // We don't exclude results in this so if we do a count we don't need to add this complex clause |
3da68f0a RK |
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"' | |
3d4e112d | 261 | } else if ([ 'best', 'hot' ].includes(options.trendingAlgorithm)) { |
3da68f0a RK |
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 | * | |
923d3d5a RK |
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 | |
3da68f0a | 274 | * see https://github.com/reddit-archive/reddit/blob/master/r2/r2/lib/db/_sorts.pyx#L47-L58 |
5a22296b | 275 | * - we have less interactions than on reddit, so multiply weights by an arbitrary factor |
3da68f0a RK |
276 | */ |
277 | const weights = { | |
5a22296b C |
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 | |
3da68f0a RK |
283 | } |
284 | ||
285 | joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"') | |
286 | ||
3d4e112d | 287 | let attribute = |
3da68f0a | 288 | `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+) |
3d4e112d | 289 | `+ LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-) |
3da68f0a RK |
290 | `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+) |
291 | `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id"))) * ${weights.comment} ` + // comments (+) | |
5a22296b | 292 | '+ (SELECT (EXTRACT(epoch FROM "video"."publishedAt") - 1446156582) / 47000) ' // base score (in number of half-days) |
3d4e112d RK |
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) | |
6b842050 | 305 | |
3da68f0a RK |
306 | group = 'GROUP BY "video"."id"' |
307 | } | |
5f3e2425 C |
308 | } |
309 | ||
310 | if (options.historyOfUser) { | |
3d4e112d | 311 | joins.push('INNER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId"') |
5f3e2425 C |
312 | |
313 | and.push('"userVideoHistory"."userId" = :historyOfUser') | |
6b842050 | 314 | replacements.historyOfUser = options.historyOfUser.id |
5f3e2425 C |
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) { | |
16c016e8 C |
348 | const escapedSearch = sequelize.escape(options.search) |
349 | const escapedLikeSearch = sequelize.escape('%' + options.search + '%') | |
5f3e2425 | 350 | |
6b842050 C |
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 | ||
5f3e2425 | 363 | let base = '(' + |
6b842050 | 364 | ' "trigramSearch"."id" IS NOT NULL OR ' + |
5f3e2425 C |
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 | ||
6b842050 | 379 | attributes.push(`COALESCE("trigramSearch"."similarity", 0) as similarity`) |
5f3e2425 C |
380 | } else { |
381 | attributes.push('0 as similarity') | |
382 | } | |
383 | ||
384 | if (options.isCount === true) attributes = [ 'COUNT(*) as "total"' ] | |
385 | ||
6b842050 C |
386 | let suffix = '' |
387 | let order = '' | |
5f3e2425 | 388 | if (options.isCount !== true) { |
5f3e2425 | 389 | |
fab67463 | 390 | if (exists(options.sort)) { |
2fd59d7d C |
391 | if (options.sort === '-originallyPublishedAt' || options.sort === 'originallyPublishedAt') { |
392 | attributes.push('COALESCE("video"."originallyPublishedAt", "video"."publishedAt") AS "publishedAtForOrder"') | |
393 | } | |
394 | ||
811cef14 | 395 | order = buildOrder(options.sort) |
fab67463 C |
396 | suffix += `${order} ` |
397 | } | |
398 | ||
399 | if (exists(options.count)) { | |
400 | const count = parseInt(options.count + '', 10) | |
401 | suffix += `LIMIT ${count} ` | |
402 | } | |
6b842050 | 403 | |
fab67463 C |
404 | if (exists(options.start)) { |
405 | const start = parseInt(options.start + '', 10) | |
406 | suffix += `OFFSET ${start} ` | |
407 | } | |
5f3e2425 C |
408 | } |
409 | ||
6b842050 C |
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 } | |
5f3e2425 C |
423 | } |
424 | ||
811cef14 | 425 | function buildOrder (value: string) { |
5f3e2425 | 426 | const { direction, field } = buildDirectionAndField(value) |
6b842050 | 427 | if (field.match(/^[a-zA-Z."]+$/) === null) throw new Error('Invalid sort column ' + field) |
5f3e2425 C |
428 | |
429 | if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()' | |
430 | ||
3d4e112d | 431 | if ([ 'trending', 'hot', 'best' ].includes(field.toLowerCase())) { // Sort by aggregation |
5bcbcbe3 | 432 | return `ORDER BY "score" ${direction}, "video"."views" ${direction}` |
5f3e2425 C |
433 | } |
434 | ||
435 | let firstSort: string | |
436 | ||
437 | if (field.toLowerCase() === 'match') { // Search | |
438 | firstSort = '"similarity"' | |
2fd59d7d C |
439 | } else if (field === 'originallyPublishedAt') { |
440 | firstSort = '"publishedAtForOrder"' | |
6b842050 C |
441 | } else if (field.includes('.')) { |
442 | firstSort = field | |
5f3e2425 C |
443 | } else { |
444 | firstSort = `"video"."${field}"` | |
445 | } | |
446 | ||
447 | return `ORDER BY ${firstSort} ${direction}, "video"."id" ASC` | |
448 | } | |
449 | ||
6b842050 C |
450 | function 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"', | |
f4796856 C |
499 | 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Actor->Avatar" ' + |
500 | 'ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id"', | |
6b842050 C |
501 | |
502 | 'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' + | |
503 | 'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"', | |
504 | ||
f4796856 | 505 | 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatar" ' + |
6b842050 C |
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) { | |
97816649 C |
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 | ) | |
6b842050 C |
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"', | |
90a8bd30 C |
527 | '"VideoFiles"."filename"': '"VideoFiles.filename"', |
528 | '"VideoFiles"."fileUrl"': '"VideoFiles.fileUrl"', | |
529 | '"VideoFiles"."torrentFilename"': '"VideoFiles.torrentFilename"', | |
530 | '"VideoFiles"."torrentUrl"': '"VideoFiles.torrentUrl"', | |
6b842050 C |
531 | '"VideoFiles"."infoHash"': '"VideoFiles.infoHash"', |
532 | '"VideoFiles"."fps"': '"VideoFiles.fps"', | |
97816649 C |
533 | '"VideoFiles"."videoId"': '"VideoFiles.videoId"', |
534 | ||
535 | '"VideoStreamingPlaylists"."id"': '"VideoStreamingPlaylists.id"', | |
7a499487 C |
536 | '"VideoStreamingPlaylists"."playlistUrl"': '"VideoStreamingPlaylists.playlistUrl"', |
537 | '"VideoStreamingPlaylists"."type"': '"VideoStreamingPlaylists.type"', | |
97816649 C |
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"', | |
90a8bd30 C |
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"', | |
97816649 C |
548 | '"VideoStreamingPlaylists->VideoFiles"."infoHash"': '"VideoStreamingPlaylists.VideoFiles.infoHash"', |
549 | '"VideoStreamingPlaylists->VideoFiles"."fps"': '"VideoStreamingPlaylists.VideoFiles.fps"', | |
7a499487 | 550 | '"VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId"': '"VideoStreamingPlaylists.VideoFiles.videoStreamingPlaylistId"', |
97816649 | 551 | '"VideoStreamingPlaylists->VideoFiles"."videoId"': '"VideoStreamingPlaylists.VideoFiles.videoId"' |
6b842050 C |
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 | ||
5f3e2425 | 596 | export { |
6b842050 C |
597 | buildListQuery, |
598 | wrapForAPIResults | |
5f3e2425 | 599 | } |