aboutsummaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2019-12-23 09:19:40 +0100
committerChocobozzz <me@florianbigard.com>2019-12-23 13:44:23 +0100
commit1c5fed88c5a24d7870550167421af2c4174aaa21 (patch)
tree9172ddadcaf34a863915c3e6d18d33307cdf61af
parent5def76ebba937594c77070dba14aff8f168ced24 (diff)
downloadPeerTube-1c5fed88c5a24d7870550167421af2c4174aaa21.tar.gz
PeerTube-1c5fed88c5a24d7870550167421af2c4174aaa21.tar.zst
PeerTube-1c5fed88c5a24d7870550167421af2c4174aaa21.zip
Litte SQL optimzation in videos list
-rw-r--r--server/models/video/video.ts48
1 files changed, 24 insertions, 24 deletions
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index 7e18af497..316a66f35 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -143,6 +143,7 @@ import { MThumbnail } from '../../typings/models/video/thumbnail'
143import { VideoFile } from '@shared/models/videos/video-file.model' 143import { VideoFile } from '@shared/models/videos/video-file.model'
144import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths' 144import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths'
145import * as validator from 'validator' 145import * as validator from 'validator'
146import { ActorFollowModel } from '@server/models/activitypub/actor-follow'
146 147
147// FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation 148// FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation
148const indexes: (ModelIndexesOptions & { where?: WhereOptions })[] = [ 149const indexes: (ModelIndexesOptions & { where?: WhereOptions })[] = [
@@ -440,36 +441,35 @@ export type AvailableForListIDsOptions = {
440 } 441 }
441 442
442 if (options.followerActorId) { 443 if (options.followerActorId) {
443 let localVideosReq = '' 444 let localVideosReq: WhereOptions = {}
444 if (options.includeLocalVideos === true) { 445 if (options.includeLocalVideos === true) {
445 localVideosReq = ' UNION ALL ' + 446 localVideosReq = { remote: false }
446 'SELECT "video"."id" AS "id" FROM "video" ' +
447 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
448 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
449 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
450 'WHERE "actor"."serverId" IS NULL'
451 } 447 }
452 448
453 // Force actorId to be a number to avoid SQL injections 449 // Force actorId to be a number to avoid SQL injections
454 const actorIdNumber = parseInt(options.followerActorId.toString(), 10) 450 const actorIdNumber = parseInt(options.followerActorId.toString(), 10)
455 whereAnd.push({ 451 whereAnd.push({
456 id: { 452 [Op.or]: [
457 [ Op.in ]: Sequelize.literal( 453 {
458 '(' + 454 id: {
459 'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' + 455 [ Op.in ]: Sequelize.literal(
460 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' + 456 '(' +
461 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + 457 'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
462 ' UNION ALL ' + 458 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
463 'SELECT "video"."id" AS "id" FROM "video" ' + 459 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
464 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' + 460 ' UNION ALL ' +
465 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' + 461 'SELECT "video"."id" AS "id" FROM "video" ' +
466 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' + 462 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
467 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' + 463 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
468 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + 464 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
469 localVideosReq + 465 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
470 ')' 466 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
471 ) 467 ')'
472 } 468 )
469 }
470 },
471 localVideosReq
472 ]
473 }) 473 })
474 } 474 }
475 475