diff options
-rw-r--r-- | server/models/account/user-video-history.ts | 2 | ||||
-rw-r--r-- | server/models/video/video-query-builder.ts | 218 | ||||
-rw-r--r-- | server/models/video/video.ts | 157 |
3 files changed, 292 insertions, 85 deletions
diff --git a/server/models/account/user-video-history.ts b/server/models/account/user-video-history.ts index 3fe4c8db1..522eebeaf 100644 --- a/server/models/account/user-video-history.ts +++ b/server/models/account/user-video-history.ts | |||
@@ -59,7 +59,7 @@ export class UserVideoHistoryModel extends Model<UserVideoHistoryModel> { | |||
59 | return VideoModel.listForApi({ | 59 | return VideoModel.listForApi({ |
60 | start, | 60 | start, |
61 | count, | 61 | count, |
62 | sort: '-UserVideoHistories.updatedAt', | 62 | sort: '-"userVideoHistory"."updatedAt"', |
63 | nsfw: null, // All | 63 | nsfw: null, // All |
64 | includeLocalVideos: true, | 64 | includeLocalVideos: true, |
65 | withFiles: false, | 65 | withFiles: false, |
diff --git a/server/models/video/video-query-builder.ts b/server/models/video/video-query-builder.ts index c4b31e58e..61f628c06 100644 --- a/server/models/video/video-query-builder.ts +++ b/server/models/video/video-query-builder.ts | |||
@@ -52,9 +52,9 @@ export type BuildVideosQueryOptions = { | |||
52 | 52 | ||
53 | function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) { | 53 | function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) { |
54 | const and: string[] = [] | 54 | const and: string[] = [] |
55 | const cte: string[] = [] | ||
56 | const joins: string[] = [] | 55 | const joins: string[] = [] |
57 | const replacements: any = {} | 56 | const replacements: any = {} |
57 | const cte: string[] = [] | ||
58 | 58 | ||
59 | let attributes: string[] = options.attributes || [ '"video"."id"' ] | 59 | let attributes: string[] = options.attributes || [ '"video"."id"' ] |
60 | let group = options.group || '' | 60 | let group = options.group || '' |
@@ -63,7 +63,7 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) | |||
63 | joins.push( | 63 | joins.push( |
64 | 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"' + | 64 | 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"' + |
65 | 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"' + | 65 | 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"' + |
66 | 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id"' | 66 | 'INNER JOIN "actor" "accountActor" ON "account"."actorId" = "accountActor"."id"' |
67 | ) | 67 | ) |
68 | 68 | ||
69 | and.push('"video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")') | 69 | and.push('"video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")') |
@@ -72,33 +72,19 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) | |||
72 | const blockerIds = [ options.serverAccountId ] | 72 | const blockerIds = [ options.serverAccountId ] |
73 | if (options.user) blockerIds.push(options.user.Account.id) | 73 | if (options.user) blockerIds.push(options.user.Account.id) |
74 | 74 | ||
75 | cte.push( | 75 | const inClause = createSafeIn(model, blockerIds) |
76 | '"mutedAccount" AS (' + | ||
77 | ' SELECT "targetAccountId" AS "id"' + | ||
78 | ' FROM "accountBlocklist"' + | ||
79 | ' WHERE "accountId" IN (' + createSafeIn(model, blockerIds) + ')' + | ||
80 | ' UNION ALL' + | ||
81 | ' SELECT "account"."id" AS "id"' + | ||
82 | ' FROM account' + | ||
83 | ' INNER JOIN "actor" ON account."actorId" = actor.id' + | ||
84 | ' INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId"' + | ||
85 | ' WHERE "serverBlocklist"."accountId" IN (' + createSafeIn(model, blockerIds) + ')' + | ||
86 | ')' | ||
87 | ) | ||
88 | |||
89 | cte.push( | ||
90 | '"mutedChannel" AS (' + | ||
91 | ' SELECT "videoChannel"."id"' + | ||
92 | ' FROM "videoChannel"' + | ||
93 | ' INNER JOIN "mutedAccount" ON "mutedAccount"."id" = "videoChannel"."accountId"' + | ||
94 | ' )' | ||
95 | ) | ||
96 | 76 | ||
97 | and.push( | 77 | and.push( |
98 | '"video"."channelId" NOT IN (SELECT "id" FROM "mutedChannel")' | 78 | 'NOT EXISTS (' + |
79 | ' SELECT 1 FROM "accountBlocklist" ' + | ||
80 | ' WHERE "accountBlocklist"."accountId" IN (' + inClause + ') ' + | ||
81 | ' AND "accountBlocklist"."targetAccountId" = "account"."id" ' + | ||
82 | ')' + | ||
83 | 'AND NOT EXISTS (' + | ||
84 | ' SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN (' + inClause + ') ' + | ||
85 | ' AND "serverBlocklist"."targetServerId" = "accountActor"."serverId"' + | ||
86 | ')' | ||
99 | ) | 87 | ) |
100 | |||
101 | replacements.videoChannelId = options.videoChannelId | ||
102 | } | 88 | } |
103 | 89 | ||
104 | // Only list public/published videos | 90 | // Only list public/published videos |
@@ -153,7 +139,7 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) | |||
153 | ' OR' + | 139 | ' OR' + |
154 | ' EXISTS (' + | 140 | ' EXISTS (' + |
155 | ' SELECT 1 from "actorFollow" ' + | 141 | ' SELECT 1 from "actorFollow" ' + |
156 | ' WHERE "actorFollow"."targetActorId" = "actor"."id" AND "actorFollow"."actorId" = :followerActorId' + | 142 | ' WHERE "actorFollow"."targetActorId" = "videoChannel"."actorId" AND "actorFollow"."actorId" = :followerActorId' + |
157 | ' )' | 143 | ' )' |
158 | 144 | ||
159 | if (options.includeLocalVideos) { | 145 | if (options.includeLocalVideos) { |
@@ -242,6 +228,8 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) | |||
242 | joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate') | 228 | joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate') |
243 | replacements.viewsGteDate = viewsGteDate | 229 | replacements.viewsGteDate = viewsGteDate |
244 | 230 | ||
231 | attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "videoViewsSum"') | ||
232 | |||
245 | group = 'GROUP BY "video"."id"' | 233 | group = 'GROUP BY "video"."id"' |
246 | } | 234 | } |
247 | 235 | ||
@@ -249,7 +237,7 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) | |||
249 | joins.push('INNER JOIN "userVideoHistory" on "video"."id" = "userVideoHistory"."videoId"') | 237 | joins.push('INNER JOIN "userVideoHistory" on "video"."id" = "userVideoHistory"."videoId"') |
250 | 238 | ||
251 | and.push('"userVideoHistory"."userId" = :historyOfUser') | 239 | and.push('"userVideoHistory"."userId" = :historyOfUser') |
252 | replacements.historyOfUser = options.historyOfUser | 240 | replacements.historyOfUser = options.historyOfUser.id |
253 | } | 241 | } |
254 | 242 | ||
255 | if (options.startDate) { | 243 | if (options.startDate) { |
@@ -286,9 +274,20 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) | |||
286 | const escapedSearch = model.sequelize.escape(options.search) | 274 | const escapedSearch = model.sequelize.escape(options.search) |
287 | const escapedLikeSearch = model.sequelize.escape('%' + options.search + '%') | 275 | const escapedLikeSearch = model.sequelize.escape('%' + options.search + '%') |
288 | 276 | ||
277 | cte.push( | ||
278 | '"trigramSearch" AS (' + | ||
279 | ' SELECT "video"."id", ' + | ||
280 | ` similarity(lower(immutable_unaccent("video"."name")), lower(immutable_unaccent(${escapedSearch}))) as similarity ` + | ||
281 | ' FROM "video" ' + | ||
282 | ' WHERE lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' + | ||
283 | ' lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' + | ||
284 | ')' | ||
285 | ) | ||
286 | |||
287 | joins.push('LEFT JOIN "trigramSearch" ON "video"."id" = "trigramSearch"."id"') | ||
288 | |||
289 | let base = '(' + | 289 | let base = '(' + |
290 | ' lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' + | 290 | ' "trigramSearch"."id" IS NOT NULL OR ' + |
291 | ' lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + ')) OR ' + | ||
292 | ' EXISTS (' + | 291 | ' EXISTS (' + |
293 | ' SELECT 1 FROM "videoTag" ' + | 292 | ' SELECT 1 FROM "videoTag" ' + |
294 | ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + | 293 | ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + |
@@ -303,50 +302,57 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) | |||
303 | base += ')' | 302 | base += ')' |
304 | and.push(base) | 303 | and.push(base) |
305 | 304 | ||
306 | attributes.push(`similarity(lower(immutable_unaccent("video"."name")), lower(immutable_unaccent(${escapedSearch}))) as similarity`) | 305 | attributes.push(`COALESCE("trigramSearch"."similarity", 0) as similarity`) |
307 | } else { | 306 | } else { |
308 | attributes.push('0 as similarity') | 307 | attributes.push('0 as similarity') |
309 | } | 308 | } |
310 | 309 | ||
311 | if (options.isCount === true) attributes = [ 'COUNT(*) as "total"' ] | 310 | if (options.isCount === true) attributes = [ 'COUNT(*) as "total"' ] |
312 | 311 | ||
313 | const cteString = cte.length !== 0 | 312 | let suffix = '' |
314 | ? 'WITH ' + cte.join(', ') + ' ' | 313 | let order = '' |
315 | : '' | ||
316 | |||
317 | let query = cteString + | ||
318 | 'SELECT ' + attributes.join(', ') + ' ' + | ||
319 | 'FROM "video" ' + joins.join(' ') + ' ' + | ||
320 | 'WHERE ' + and.join(' AND ') + ' ' + | ||
321 | group + ' ' + | ||
322 | having + ' ' | ||
323 | |||
324 | if (options.isCount !== true) { | 314 | if (options.isCount !== true) { |
325 | const count = parseInt(options.count + '', 10) | 315 | const count = parseInt(options.count + '', 10) |
326 | const start = parseInt(options.start + '', 10) | 316 | const start = parseInt(options.start + '', 10) |
327 | 317 | ||
328 | query += buildOrder(model, options.sort) + ' ' + | 318 | order = buildOrder(model, options.sort) |
319 | |||
320 | suffix = order + ' ' + | ||
329 | 'LIMIT ' + count + ' ' + | 321 | 'LIMIT ' + count + ' ' + |
330 | 'OFFSET ' + start | 322 | 'OFFSET ' + start |
331 | } | 323 | } |
332 | 324 | ||
333 | return { query, replacements } | 325 | const cteString = cte.length !== 0 |
326 | ? `WITH ${cte.join(', ')} ` | ||
327 | : '' | ||
328 | |||
329 | const query = cteString + | ||
330 | 'SELECT ' + attributes.join(', ') + ' ' + | ||
331 | 'FROM "video" ' + joins.join(' ') + ' ' + | ||
332 | 'WHERE ' + and.join(' AND ') + ' ' + | ||
333 | group + ' ' + | ||
334 | having + ' ' + | ||
335 | suffix | ||
336 | |||
337 | return { query, replacements, order } | ||
334 | } | 338 | } |
335 | 339 | ||
336 | function buildOrder (model: typeof Model, value: string) { | 340 | function buildOrder (model: typeof Model, value: string) { |
337 | const { direction, field } = buildDirectionAndField(value) | 341 | const { direction, field } = buildDirectionAndField(value) |
338 | if (field.match(/^[a-zA-Z]+$/) === null) throw new Error('Invalid sort column ' + field) | 342 | if (field.match(/^[a-zA-Z."]+$/) === null) throw new Error('Invalid sort column ' + field) |
339 | 343 | ||
340 | if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()' | 344 | if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()' |
341 | 345 | ||
342 | if (field.toLowerCase() === 'trending') { // Sort by aggregation | 346 | if (field.toLowerCase() === 'trending') { // Sort by aggregation |
343 | return `ORDER BY COALESCE(SUM("videoView"."views"), 0) ${direction}, "video"."views" ${direction}` | 347 | return `ORDER BY "videoViewsSum" ${direction}, "video"."views" ${direction}` |
344 | } | 348 | } |
345 | 349 | ||
346 | let firstSort: string | 350 | let firstSort: string |
347 | 351 | ||
348 | if (field.toLowerCase() === 'match') { // Search | 352 | if (field.toLowerCase() === 'match') { // Search |
349 | firstSort = '"similarity"' | 353 | firstSort = '"similarity"' |
354 | } else if (field.includes('.')) { | ||
355 | firstSort = field | ||
350 | } else { | 356 | } else { |
351 | firstSort = `"video"."${field}"` | 357 | firstSort = `"video"."${field}"` |
352 | } | 358 | } |
@@ -354,6 +360,124 @@ function buildOrder (model: typeof Model, value: string) { | |||
354 | return `ORDER BY ${firstSort} ${direction}, "video"."id" ASC` | 360 | return `ORDER BY ${firstSort} ${direction}, "video"."id" ASC` |
355 | } | 361 | } |
356 | 362 | ||
363 | function wrapForAPIResults (baseQuery: string, replacements: any, options: BuildVideosQueryOptions, order: string) { | ||
364 | const attributes = { | ||
365 | '"video".*': '', | ||
366 | '"VideoChannel"."id"': '"VideoChannel.id"', | ||
367 | '"VideoChannel"."name"': '"VideoChannel.name"', | ||
368 | '"VideoChannel"."description"': '"VideoChannel.description"', | ||
369 | '"VideoChannel"."actorId"': '"VideoChannel.actorId"', | ||
370 | '"VideoChannel->Actor"."id"': '"VideoChannel.Actor.id"', | ||
371 | '"VideoChannel->Actor"."preferredUsername"': '"VideoChannel.Actor.preferredUsername"', | ||
372 | '"VideoChannel->Actor"."url"': '"VideoChannel.Actor.url"', | ||
373 | '"VideoChannel->Actor"."serverId"': '"VideoChannel.Actor.serverId"', | ||
374 | '"VideoChannel->Actor"."avatarId"': '"VideoChannel.Actor.avatarId"', | ||
375 | '"VideoChannel->Account"."id"': '"VideoChannel.Account.id"', | ||
376 | '"VideoChannel->Account"."name"': '"VideoChannel.Account.name"', | ||
377 | '"VideoChannel->Account->Actor"."id"': '"VideoChannel.Account.Actor.id"', | ||
378 | '"VideoChannel->Account->Actor"."preferredUsername"': '"VideoChannel.Account.Actor.preferredUsername"', | ||
379 | '"VideoChannel->Account->Actor"."url"': '"VideoChannel.Account.Actor.url"', | ||
380 | '"VideoChannel->Account->Actor"."serverId"': '"VideoChannel.Account.Actor.serverId"', | ||
381 | '"VideoChannel->Account->Actor"."avatarId"': '"VideoChannel.Account.Actor.avatarId"', | ||
382 | '"VideoChannel->Actor->Server"."id"': '"VideoChannel.Actor.Server.id"', | ||
383 | '"VideoChannel->Actor->Server"."host"': '"VideoChannel.Actor.Server.host"', | ||
384 | '"VideoChannel->Actor->Avatar"."id"': '"VideoChannel.Actor.Avatar.id"', | ||
385 | '"VideoChannel->Actor->Avatar"."filename"': '"VideoChannel.Actor.Avatar.filename"', | ||
386 | '"VideoChannel->Actor->Avatar"."fileUrl"': '"VideoChannel.Actor.Avatar.fileUrl"', | ||
387 | '"VideoChannel->Actor->Avatar"."onDisk"': '"VideoChannel.Actor.Avatar.onDisk"', | ||
388 | '"VideoChannel->Actor->Avatar"."createdAt"': '"VideoChannel.Actor.Avatar.createdAt"', | ||
389 | '"VideoChannel->Actor->Avatar"."updatedAt"': '"VideoChannel.Actor.Avatar.updatedAt"', | ||
390 | '"VideoChannel->Account->Actor->Server"."id"': '"VideoChannel.Account.Actor.Server.id"', | ||
391 | '"VideoChannel->Account->Actor->Server"."host"': '"VideoChannel.Account.Actor.Server.host"', | ||
392 | '"VideoChannel->Account->Actor->Avatar"."id"': '"VideoChannel.Account.Actor.Avatar.id"', | ||
393 | '"VideoChannel->Account->Actor->Avatar"."filename"': '"VideoChannel.Account.Actor.Avatar.filename"', | ||
394 | '"VideoChannel->Account->Actor->Avatar"."fileUrl"': '"VideoChannel.Account.Actor.Avatar.fileUrl"', | ||
395 | '"VideoChannel->Account->Actor->Avatar"."onDisk"': '"VideoChannel.Account.Actor.Avatar.onDisk"', | ||
396 | '"VideoChannel->Account->Actor->Avatar"."createdAt"': '"VideoChannel.Account.Actor.Avatar.createdAt"', | ||
397 | '"VideoChannel->Account->Actor->Avatar"."updatedAt"': '"VideoChannel.Account.Actor.Avatar.updatedAt"', | ||
398 | '"Thumbnails"."id"': '"Thumbnails.id"', | ||
399 | '"Thumbnails"."type"': '"Thumbnails.type"', | ||
400 | '"Thumbnails"."filename"': '"Thumbnails.filename"' | ||
401 | } | ||
402 | |||
403 | const joins = [ | ||
404 | 'INNER JOIN "video" ON "tmp"."id" = "video"."id"', | ||
405 | |||
406 | 'INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id"', | ||
407 | 'INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id"', | ||
408 | 'INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"', | ||
409 | 'INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id"', | ||
410 | |||
411 | 'LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id"', | ||
412 | 'LEFT OUTER JOIN "avatar" AS "VideoChannel->Actor->Avatar" ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id"', | ||
413 | |||
414 | 'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' + | ||
415 | 'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"', | ||
416 | |||
417 | 'LEFT OUTER JOIN "avatar" AS "VideoChannel->Account->Actor->Avatar" ' + | ||
418 | 'ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id"', | ||
419 | |||
420 | 'LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId"' | ||
421 | ] | ||
422 | |||
423 | if (options.withFiles) { | ||
424 | joins.push('INNER JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"') | ||
425 | |||
426 | Object.assign(attributes, { | ||
427 | '"VideoFiles"."id"': '"VideoFiles.id"', | ||
428 | '"VideoFiles"."createdAt"': '"VideoFiles.createdAt"', | ||
429 | '"VideoFiles"."updatedAt"': '"VideoFiles.updatedAt"', | ||
430 | '"VideoFiles"."resolution"': '"VideoFiles.resolution"', | ||
431 | '"VideoFiles"."size"': '"VideoFiles.size"', | ||
432 | '"VideoFiles"."extname"': '"VideoFiles.extname"', | ||
433 | '"VideoFiles"."infoHash"': '"VideoFiles.infoHash"', | ||
434 | '"VideoFiles"."fps"': '"VideoFiles.fps"', | ||
435 | '"VideoFiles"."videoId"': '"VideoFiles.videoId"' | ||
436 | }) | ||
437 | } | ||
438 | |||
439 | if (options.user) { | ||
440 | joins.push( | ||
441 | 'LEFT OUTER JOIN "userVideoHistory" ' + | ||
442 | 'ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :userVideoHistoryId' | ||
443 | ) | ||
444 | replacements.userVideoHistoryId = options.user.id | ||
445 | |||
446 | Object.assign(attributes, { | ||
447 | '"userVideoHistory"."id"': '"userVideoHistory.id"', | ||
448 | '"userVideoHistory"."currentTime"': '"userVideoHistory.currentTime"' | ||
449 | }) | ||
450 | } | ||
451 | |||
452 | if (options.videoPlaylistId) { | ||
453 | joins.push( | ||
454 | 'INNER JOIN "videoPlaylistElement" as "VideoPlaylistElement" ON "videoPlaylistElement"."videoId" = "video"."id" ' + | ||
455 | 'AND "VideoPlaylistElement"."videoPlaylistId" = :videoPlaylistId' | ||
456 | ) | ||
457 | replacements.videoPlaylistId = options.videoPlaylistId | ||
458 | |||
459 | Object.assign(attributes, { | ||
460 | '"VideoPlaylistElement"."createdAt"': '"VideoPlaylistElement.createdAt"', | ||
461 | '"VideoPlaylistElement"."updatedAt"': '"VideoPlaylistElement.updatedAt"', | ||
462 | '"VideoPlaylistElement"."url"': '"VideoPlaylistElement.url"', | ||
463 | '"VideoPlaylistElement"."position"': '"VideoPlaylistElement.position"', | ||
464 | '"VideoPlaylistElement"."startTimestamp"': '"VideoPlaylistElement.startTimestamp"', | ||
465 | '"VideoPlaylistElement"."stopTimestamp"': '"VideoPlaylistElement.stopTimestamp"', | ||
466 | '"VideoPlaylistElement"."videoPlaylistId"': '"VideoPlaylistElement.videoPlaylistId"' | ||
467 | }) | ||
468 | } | ||
469 | |||
470 | const select = 'SELECT ' + Object.keys(attributes).map(key => { | ||
471 | const value = attributes[key] | ||
472 | if (value) return `${key} AS ${value}` | ||
473 | |||
474 | return key | ||
475 | }).join(', ') | ||
476 | |||
477 | return `${select} FROM (${baseQuery}) AS "tmp" ${joins.join(' ')} ${order}` | ||
478 | } | ||
479 | |||
357 | export { | 480 | export { |
358 | buildListQuery | 481 | buildListQuery, |
482 | wrapForAPIResults | ||
359 | } | 483 | } |
diff --git a/server/models/video/video.ts b/server/models/video/video.ts index 217ca8e50..7f94e834a 100644 --- a/server/models/video/video.ts +++ b/server/models/video/video.ts | |||
@@ -1,5 +1,5 @@ | |||
1 | import * as Bluebird from 'bluebird' | 1 | import * as Bluebird from 'bluebird' |
2 | import { maxBy, minBy } from 'lodash' | 2 | import { maxBy, minBy, pick } from 'lodash' |
3 | import { join } from 'path' | 3 | import { join } from 'path' |
4 | import { FindOptions, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' | 4 | import { FindOptions, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' |
5 | import { | 5 | import { |
@@ -124,7 +124,7 @@ import { MThumbnail } from '../../typings/models/video/thumbnail' | |||
124 | import { VideoFile } from '@shared/models/videos/video-file.model' | 124 | import { VideoFile } from '@shared/models/videos/video-file.model' |
125 | import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths' | 125 | import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths' |
126 | import { ModelCache } from '@server/models/model-cache' | 126 | import { ModelCache } from '@server/models/model-cache' |
127 | import { buildListQuery, BuildVideosQueryOptions } from './video-query-builder' | 127 | import { buildListQuery, BuildVideosQueryOptions, wrapForAPIResults } from './video-query-builder' |
128 | 128 | ||
129 | export enum ScopeNames { | 129 | export enum ScopeNames { |
130 | AVAILABLE_FOR_LIST_IDS = 'AVAILABLE_FOR_LIST_IDS', | 130 | AVAILABLE_FOR_LIST_IDS = 'AVAILABLE_FOR_LIST_IDS', |
@@ -1408,19 +1408,25 @@ export class VideoModel extends Model<VideoModel> { | |||
1408 | options: BuildVideosQueryOptions, | 1408 | options: BuildVideosQueryOptions, |
1409 | countVideos = true | 1409 | countVideos = true |
1410 | ) { | 1410 | ) { |
1411 | const { query, replacements } = buildListQuery(VideoModel, options) | 1411 | function getCount () { |
1412 | const { query: queryCount, replacements: replacementsCount } = buildListQuery(VideoModel, Object.assign({}, options, { isCount: true })) | 1412 | if (countVideos !== true) return Promise.resolve(undefined) |
1413 | 1413 | ||
1414 | const [ count, rows ] = await Promise.all([ | 1414 | const countOptions = Object.assign({}, options, { isCount: true }) |
1415 | countVideos | 1415 | const { query: queryCount, replacements: replacementsCount } = buildListQuery(VideoModel, countOptions) |
1416 | ? this.sequelize.query<any>(queryCount, { replacements: replacementsCount, type: QueryTypes.SELECT }) | ||
1417 | .then(rows => rows.length !== 0 ? rows[0].total : 0) | ||
1418 | : Promise.resolve<number>(undefined), | ||
1419 | 1416 | ||
1420 | this.sequelize.query<any>(query, { replacements, type: QueryTypes.SELECT }) | 1417 | return VideoModel.sequelize.query<any>(queryCount, { replacements: replacementsCount, type: QueryTypes.SELECT }) |
1421 | .then(rows => rows.map(r => r.id)) | 1418 | .then(rows => rows.length !== 0 ? rows[0].total : 0) |
1422 | .then(ids => VideoModel.loadCompleteVideosForApi(ids, options)) | 1419 | } |
1423 | ]) | 1420 | |
1421 | function getModels () { | ||
1422 | const { query, replacements, order } = buildListQuery(VideoModel, options) | ||
1423 | const queryModels = wrapForAPIResults(query, replacements, options, order) | ||
1424 | |||
1425 | return VideoModel.sequelize.query<any>(queryModels, { replacements, type: QueryTypes.SELECT, nest: true }) | ||
1426 | .then(rows => VideoModel.buildAPIResult(rows)) | ||
1427 | } | ||
1428 | |||
1429 | const [ count, rows ] = await Promise.all([ getCount(), getModels() ]) | ||
1424 | 1430 | ||
1425 | return { | 1431 | return { |
1426 | data: rows, | 1432 | data: rows, |
@@ -1428,36 +1434,113 @@ export class VideoModel extends Model<VideoModel> { | |||
1428 | } | 1434 | } |
1429 | } | 1435 | } |
1430 | 1436 | ||
1431 | private static loadCompleteVideosForApi (ids: number[], options: BuildVideosQueryOptions) { | 1437 | private static buildAPIResult (rows: any[]) { |
1432 | if (ids.length === 0) return [] | 1438 | const memo: { [ id: number ]: VideoModel } = {} |
1439 | |||
1440 | const thumbnailsDone = new Set<number>() | ||
1441 | const historyDone = new Set<number>() | ||
1442 | const videoFilesDone = new Set<number>() | ||
1443 | |||
1444 | const videos: VideoModel[] = [] | ||
1445 | |||
1446 | const avatarKeys = [ 'id', 'filename', 'fileUrl', 'onDisk', 'createdAt', 'updatedAt' ] | ||
1447 | const actorKeys = [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ] | ||
1448 | const serverKeys = [ 'id', 'host' ] | ||
1449 | const videoFileKeys = [ 'id', 'createdAt', 'updatedAt', 'resolution', 'size', 'extname', 'infoHash', 'fps', 'videoId' ] | ||
1450 | const videoKeys = [ | ||
1451 | 'id', | ||
1452 | 'uuid', | ||
1453 | 'name', | ||
1454 | 'category', | ||
1455 | 'licence', | ||
1456 | 'language', | ||
1457 | 'privacy', | ||
1458 | 'nsfw', | ||
1459 | 'description', | ||
1460 | 'support', | ||
1461 | 'duration', | ||
1462 | 'views', | ||
1463 | 'likes', | ||
1464 | 'dislikes', | ||
1465 | 'remote', | ||
1466 | 'url', | ||
1467 | 'commentsEnabled', | ||
1468 | 'downloadEnabled', | ||
1469 | 'waitTranscoding', | ||
1470 | 'state', | ||
1471 | 'publishedAt', | ||
1472 | 'originallyPublishedAt', | ||
1473 | 'channelId', | ||
1474 | 'createdAt', | ||
1475 | 'updatedAt' | ||
1476 | ] | ||
1433 | 1477 | ||
1434 | const secondQuery: FindOptions = { | 1478 | function buildActor (rowActor: any) { |
1435 | offset: 0, | 1479 | const avatarModel = rowActor.Avatar.id !== null |
1436 | limit: options.count, | 1480 | ? new AvatarModel(pick(rowActor.Avatar, avatarKeys)) |
1437 | order: [ // Keep original order | 1481 | : null |
1438 | Sequelize.literal( | ||
1439 | ids.map(id => `"VideoModel".id = ${id} DESC`).join(', ') | ||
1440 | ) | ||
1441 | ] | ||
1442 | } | ||
1443 | 1482 | ||
1444 | const apiScope: (string | ScopeOptions)[] = [] | 1483 | const serverModel = rowActor.Server.id !== null |
1484 | ? new ServerModel(pick(rowActor.Server, serverKeys)) | ||
1485 | : null | ||
1445 | 1486 | ||
1446 | if (options.user) { | 1487 | const actorModel = new ActorModel(pick(rowActor, actorKeys)) |
1447 | apiScope.push({ method: [ ScopeNames.WITH_USER_HISTORY, options.user.id ] }) | 1488 | actorModel.Avatar = avatarModel |
1489 | actorModel.Server = serverModel | ||
1490 | |||
1491 | return actorModel | ||
1448 | } | 1492 | } |
1449 | 1493 | ||
1450 | apiScope.push({ | 1494 | for (const row of rows) { |
1451 | method: [ | 1495 | if (!memo[row.id]) { |
1452 | ScopeNames.FOR_API, { | 1496 | // Build Channel |
1453 | ids, | 1497 | const channel = row.VideoChannel |
1454 | withFiles: options.withFiles, | 1498 | const channelModel = new VideoChannelModel(pick(channel, [ 'id', 'name', 'description', 'actorId' ])) |
1455 | videoPlaylistId: options.videoPlaylistId | 1499 | channelModel.Actor = buildActor(channel.Actor) |
1456 | } as ForAPIOptions | 1500 | |
1457 | ] | 1501 | const account = row.VideoChannel.Account |
1458 | }) | 1502 | const accountModel = new AccountModel(pick(account, [ 'id', 'name' ])) |
1503 | accountModel.Actor = buildActor(account.Actor) | ||
1504 | |||
1505 | channelModel.Account = accountModel | ||
1506 | |||
1507 | const videoModel = new VideoModel(pick(row, videoKeys)) | ||
1508 | videoModel.VideoChannel = channelModel | ||
1509 | |||
1510 | videoModel.UserVideoHistories = [] | ||
1511 | videoModel.Thumbnails = [] | ||
1512 | videoModel.VideoFiles = [] | ||
1513 | |||
1514 | memo[row.id] = videoModel | ||
1515 | // Don't take object value to have a sorted array | ||
1516 | videos.push(videoModel) | ||
1517 | } | ||
1518 | |||
1519 | const videoModel = memo[row.id] | ||
1520 | |||
1521 | if (row.userVideoHistory?.id && !historyDone.has(row.userVideoHistory.id)) { | ||
1522 | const historyModel = new UserVideoHistoryModel(pick(row.userVideoHistory, [ 'id', 'currentTime' ])) | ||
1523 | videoModel.UserVideoHistories.push(historyModel) | ||
1524 | |||
1525 | historyDone.add(row.userVideoHistory.id) | ||
1526 | } | ||
1527 | |||
1528 | if (row.Thumbnails?.id && !thumbnailsDone.has(row.Thumbnails.id)) { | ||
1529 | const thumbnailModel = new ThumbnailModel(pick(row.Thumbnails, [ 'id', 'type', 'filename' ])) | ||
1530 | videoModel.Thumbnails.push(thumbnailModel) | ||
1531 | |||
1532 | thumbnailsDone.add(row.Thumbnails.id) | ||
1533 | } | ||
1534 | |||
1535 | if (row.VideoFiles?.id && !videoFilesDone.has(row.VideoFiles.id)) { | ||
1536 | const videoFileModel = new VideoFileModel(pick(row.VideoFiles, videoFileKeys)) | ||
1537 | videoModel.VideoFiles.push(videoFileModel) | ||
1538 | |||
1539 | videoFilesDone.add(row.VideoFiles.id) | ||
1540 | } | ||
1541 | } | ||
1459 | 1542 | ||
1460 | return VideoModel.scope(apiScope).findAll(secondQuery) | 1543 | return videos |
1461 | } | 1544 | } |
1462 | 1545 | ||
1463 | private static isPrivacyForFederation (privacy: VideoPrivacy) { | 1546 | private static isPrivacyForFederation (privacy: VideoPrivacy) { |