From 6b0c3c7ca917ad09a011c2821f5bd1a2485aebca Mon Sep 17 00:00:00 2001
From: Chocobozzz <me@florianbigard.com>
Date: Thu, 9 Jan 2020 09:26:59 +0100
Subject: [PATCH] Optimize list my playlists SQL query

---
 .../video-playlist/video-playlist.service.ts  | 13 +++++-
 server/controllers/api/accounts.ts            |  6 +--
 server/models/video/video-playlist.ts         | 40 ++++++++++---------
 3 files changed, 36 insertions(+), 23 deletions(-)

diff --git a/client/src/app/shared/video-playlist/video-playlist.service.ts b/client/src/app/shared/video-playlist/video-playlist.service.ts
index 1ec9315ef..078bcc5d7 100644
--- a/client/src/app/shared/video-playlist/video-playlist.service.ts
+++ b/client/src/app/shared/video-playlist/video-playlist.service.ts
@@ -42,6 +42,7 @@ export class VideoPlaylistService {
   private videoExistsCache: { [ id: number ]: VideoExistInPlaylist[] } = {}
 
   private myAccountPlaylistCache: ResultList<CachedPlaylist> = undefined
+  private myAccountPlaylistCacheRunning = false
   private myAccountPlaylistCacheSubject = new Subject<ResultList<CachedPlaylist>>()
 
   constructor (
@@ -78,12 +79,20 @@ export class VideoPlaylistService {
   }
 
   listMyPlaylistWithCache (user: AuthUser, search?: string) {
-    if (!search && this.myAccountPlaylistCache) return of(this.myAccountPlaylistCache)
+    if (!search) {
+      if (this.myAccountPlaylistCacheRunning) return
+      if (this.myAccountPlaylistCache) return of(this.myAccountPlaylistCache)
+    }
+
+    this.myAccountPlaylistCacheRunning = true
 
     return this.listAccountPlaylists(user.account, undefined, '-updatedAt', search)
                .pipe(
                  tap(result => {
-                   if (!search) this.myAccountPlaylistCache = result
+                   if (!search) {
+                     this.myAccountPlaylistCacheRunning = false
+                     this.myAccountPlaylistCache = result
+                   }
                  })
                )
   }
diff --git a/server/controllers/api/accounts.ts b/server/controllers/api/accounts.ts
index 00148ff55..05740318e 100644
--- a/server/controllers/api/accounts.ts
+++ b/server/controllers/api/accounts.ts
@@ -133,9 +133,9 @@ async function listAccountPlaylists (req: express.Request, res: express.Response
   const serverActor = await getServerActor()
 
   // Allow users to see their private/unlisted video playlists
-  let privateAndUnlisted = false
+  let listMyPlaylists = false
   if (res.locals.oauth && res.locals.oauth.token.User.Account.id === res.locals.account.id) {
-    privateAndUnlisted = true
+    listMyPlaylists = true
   }
 
   const resultList = await VideoPlaylistModel.listForApi({
@@ -145,7 +145,7 @@ async function listAccountPlaylists (req: express.Request, res: express.Response
     count: req.query.count,
     sort: req.query.sort,
     accountId: res.locals.account.id,
-    privateAndUnlisted,
+    listMyPlaylists,
     type: req.query.playlistType
   })
 
diff --git a/server/models/video/video-playlist.ts b/server/models/video/video-playlist.ts
index 71a580249..bcdda36e5 100644
--- a/server/models/video/video-playlist.ts
+++ b/server/models/video/video-playlist.ts
@@ -68,7 +68,7 @@ type AvailableForListOptions = {
   type?: VideoPlaylistType
   accountId?: number
   videoChannelId?: number
-  privateAndUnlisted?: boolean,
+  listMyPlaylists?: boolean,
   search?: string
 }
 
@@ -124,27 +124,31 @@ type AvailableForListOptions = {
     ]
   },
   [ ScopeNames.AVAILABLE_FOR_LIST ]: (options: AvailableForListOptions) => {
-    // Only list local playlists OR playlists that are on an instance followed by actorId
-    const inQueryInstanceFollow = buildServerIdsFollowedBy(options.followerActorId)
-    const whereActor = {
-      [ Op.or ]: [
-        {
-          serverId: null
-        },
-        {
-          serverId: {
-            [ Op.in ]: literal(inQueryInstanceFollow)
-          }
-        }
-      ]
-    }
+
+    let whereActor: WhereOptions = {}
 
     const whereAnd: WhereOptions[] = []
 
-    if (options.privateAndUnlisted !== true) {
+    if (options.listMyPlaylists !== true) {
       whereAnd.push({
         privacy: VideoPlaylistPrivacy.PUBLIC
       })
+
+      // Only list local playlists OR playlists that are on an instance followed by actorId
+      const inQueryInstanceFollow = buildServerIdsFollowedBy(options.followerActorId)
+
+      whereActor = {
+        [ Op.or ]: [
+          {
+            serverId: null
+          },
+          {
+            serverId: {
+              [ Op.in ]: literal(inQueryInstanceFollow)
+            }
+          }
+        ]
+      }
     }
 
     if (options.accountId) {
@@ -301,7 +305,7 @@ export class VideoPlaylistModel extends Model<VideoPlaylistModel> {
     type?: VideoPlaylistType,
     accountId?: number,
     videoChannelId?: number,
-    privateAndUnlisted?: boolean,
+    listMyPlaylists?: boolean,
     search?: string
   }) {
     const query = {
@@ -319,7 +323,7 @@ export class VideoPlaylistModel extends Model<VideoPlaylistModel> {
             followerActorId: options.followerActorId,
             accountId: options.accountId,
             videoChannelId: options.videoChannelId,
-            privateAndUnlisted: options.privateAndUnlisted,
+            listMyPlaylists: options.listMyPlaylists,
             search: options.search
           } as AvailableForListOptions
         ]
-- 
2.41.0