aboutsummaryrefslogtreecommitdiffhomepage
path: root/server
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2020-03-09 14:44:44 +0100
committerChocobozzz <me@florianbigard.com>2020-03-10 14:03:58 +0100
commit6b842050f7b0820bbd3051d9bfec1fffdf6d8df4 (patch)
tree0a3d6dd9333d910b79486dc0bbd2f6f0c868cc11 /server
parent5f3e2425f1c64d93860a0c3341de9b361b3c1f1f (diff)
downloadPeerTube-6b842050f7b0820bbd3051d9bfec1fffdf6d8df4.tar.gz
PeerTube-6b842050f7b0820bbd3051d9bfec1fffdf6d8df4.tar.zst
PeerTube-6b842050f7b0820bbd3051d9bfec1fffdf6d8df4.zip
Wrap videos list query in raw SQL
Diffstat (limited to 'server')
-rw-r--r--server/models/account/user-video-history.ts2
-rw-r--r--server/models/video/video-query-builder.ts218
-rw-r--r--server/models/video/video.ts157
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
53function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) { 53function 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
336function buildOrder (model: typeof Model, value: string) { 340function 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
363function 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
357export { 480export {
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 @@
1import * as Bluebird from 'bluebird' 1import * as Bluebird from 'bluebird'
2import { maxBy, minBy } from 'lodash' 2import { maxBy, minBy, pick } from 'lodash'
3import { join } from 'path' 3import { join } from 'path'
4import { FindOptions, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' 4import { FindOptions, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize'
5import { 5import {
@@ -124,7 +124,7 @@ import { MThumbnail } from '../../typings/models/video/thumbnail'
124import { VideoFile } from '@shared/models/videos/video-file.model' 124import { VideoFile } from '@shared/models/videos/video-file.model'
125import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths' 125import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths'
126import { ModelCache } from '@server/models/model-cache' 126import { ModelCache } from '@server/models/model-cache'
127import { buildListQuery, BuildVideosQueryOptions } from './video-query-builder' 127import { buildListQuery, BuildVideosQueryOptions, wrapForAPIResults } from './video-query-builder'
128 128
129export enum ScopeNames { 129export 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) {