From 1c5fed88c5a24d7870550167421af2c4174aaa21 Mon Sep 17 00:00:00 2001
From: Chocobozzz <me@florianbigard.com>
Date: Mon, 23 Dec 2019 09:19:40 +0100
Subject: Litte SQL optimzation in videos list

---
 server/models/video/video.ts | 48 ++++++++++++++++++++++----------------------
 1 file changed, 24 insertions(+), 24 deletions(-)

(limited to 'server/models/video')

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'
 import { VideoFile } from '@shared/models/videos/video-file.model'
 import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths'
 import * as validator from 'validator'
+import { ActorFollowModel } from '@server/models/activitypub/actor-follow'
 
 // FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation
 const indexes: (ModelIndexesOptions & { where?: WhereOptions })[] = [
@@ -440,36 +441,35 @@ export type AvailableForListIDsOptions = {
     }
 
     if (options.followerActorId) {
-      let localVideosReq = ''
+      let localVideosReq: WhereOptions = {}
       if (options.includeLocalVideos === true) {
-        localVideosReq = ' UNION ALL ' +
-          'SELECT "video"."id" AS "id" FROM "video" ' +
-          'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
-          'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
-          'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
-          'WHERE "actor"."serverId" IS NULL'
+        localVideosReq = { remote: false }
       }
 
       // Force actorId to be a number to avoid SQL injections
       const actorIdNumber = parseInt(options.followerActorId.toString(), 10)
       whereAnd.push({
-        id: {
-          [ Op.in ]: Sequelize.literal(
-            '(' +
-            'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
-            'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
-            'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
-            ' UNION ALL ' +
-            'SELECT "video"."id" AS "id" FROM "video" ' +
-            'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
-            'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
-            'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
-            'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
-            'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
-            localVideosReq +
-            ')'
-          )
-        }
+        [Op.or]: [
+          {
+            id: {
+              [ Op.in ]: Sequelize.literal(
+                '(' +
+                'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
+                'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
+                'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
+                ' UNION ALL ' +
+                'SELECT "video"."id" AS "id" FROM "video" ' +
+                'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
+                'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
+                'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
+                'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
+                'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
+                ')'
+              )
+            }
+          },
+          localVideosReq
+        ]
       })
     }
 
-- 
cgit v1.2.3