From 627621c1e8d37c33f7b3dd59f4c8907b12c630bc Mon Sep 17 00:00:00 2001
From: Chocobozzz <me@florianbigard.com>
Date: Tue, 18 Sep 2018 12:00:49 +0200
Subject: Optimize SQL requests of watch page API endpoints

---
 server/models/video/video.ts | 74 ++++++++++++++++++++++++--------------------
 1 file changed, 41 insertions(+), 33 deletions(-)

(limited to 'server/models/video/video.ts')

diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index ce856aed2..c7cd2890c 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -91,6 +91,7 @@ import {
   videoModelToFormattedDetailsJSON,
   videoModelToFormattedJSON
 } from './video-format-utils'
+import * as validator from 'validator'
 
 // FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation
 const indexes: Sequelize.DefineIndexesOptions[] = [
@@ -466,6 +467,7 @@ type AvailableForListIDsOptions = {
         required: false,
         include: [
           {
+            attributes: [ 'fileUrl' ],
             model: () => VideoRedundancyModel.unscoped(),
             required: false
           }
@@ -1062,44 +1064,34 @@ export class VideoModel extends Model<VideoModel> {
     return VideoModel.getAvailableForApi(query, queryOptions)
   }
 
-  static load (id: number, t?: Sequelize.Transaction) {
-    return VideoModel.findById(id, { transaction: t })
-  }
-
-  static loadWithFile (id: number, t?: Sequelize.Transaction, logging?: boolean) {
-    return VideoModel.scope(ScopeNames.WITH_FILES)
-                     .findById(id, { transaction: t, logging })
-  }
-
-  static loadByUrlAndPopulateAccount (url: string, t?: Sequelize.Transaction) {
-    const query: IFindOptions<VideoModel> = {
-      where: {
-        url
-      }
+  static load (id: number | string, t?: Sequelize.Transaction) {
+    const where = VideoModel.buildWhereIdOrUUID(id)
+    const options = {
+      where,
+      transaction: t
     }
 
-    if (t !== undefined) query.transaction = t
-
-    return VideoModel.scope([ ScopeNames.WITH_ACCOUNT_DETAILS, ScopeNames.WITH_FILES ]).findOne(query)
+    return VideoModel.findOne(options)
   }
 
-  static loadAndPopulateAccountAndServerAndTags (id: number) {
+  static loadOnlyId (id: number | string, t?: Sequelize.Transaction) {
+    const where = VideoModel.buildWhereIdOrUUID(id)
+
     const options = {
-      order: [ [ 'Tags', 'name', 'ASC' ] ]
+      attributes: [ 'id' ],
+      where,
+      transaction: t
     }
 
-    return VideoModel
-      .scope([
-        ScopeNames.WITH_TAGS,
-        ScopeNames.WITH_BLACKLISTED,
-        ScopeNames.WITH_FILES,
-        ScopeNames.WITH_ACCOUNT_DETAILS,
-        ScopeNames.WITH_SCHEDULED_UPDATE
-      ])
-      .findById(id, options)
+    return VideoModel.findOne(options)
+  }
+
+  static loadWithFile (id: number, t?: Sequelize.Transaction, logging?: boolean) {
+    return VideoModel.scope(ScopeNames.WITH_FILES)
+                     .findById(id, { transaction: t, logging })
   }
 
-  static loadByUUID (uuid: string) {
+  static loadByUUIDWithFile (uuid: string) {
     const options = {
       where: {
         uuid
@@ -1111,12 +1103,24 @@ export class VideoModel extends Model<VideoModel> {
       .findOne(options)
   }
 
-  static loadByUUIDAndPopulateAccountAndServerAndTags (uuid: string, t?: Sequelize.Transaction) {
+  static loadByUrlAndPopulateAccount (url: string, t?: Sequelize.Transaction) {
+    const query: IFindOptions<VideoModel> = {
+      where: {
+        url
+      }
+    }
+
+    if (t !== undefined) query.transaction = t
+
+    return VideoModel.scope([ ScopeNames.WITH_ACCOUNT_DETAILS, ScopeNames.WITH_FILES ]).findOne(query)
+  }
+
+  static loadAndPopulateAccountAndServerAndTags (id: number | string, t?: Sequelize.Transaction) {
+    const where = VideoModel.buildWhereIdOrUUID(id)
+
     const options = {
       order: [ [ 'Tags', 'name', 'ASC' ] ],
-      where: {
-        uuid
-      },
+      where,
       transaction: t
     }
 
@@ -1277,6 +1281,10 @@ export class VideoModel extends Model<VideoModel> {
     return VIDEO_STATES[ id ] || 'Unknown'
   }
 
+  static buildWhereIdOrUUID (id: number | string) {
+    return validator.isInt('' + id) ? { id } : { uuid: id }
+  }
+
   getOriginalFile () {
     if (Array.isArray(this.VideoFiles) === false) return undefined
 
-- 
cgit v1.2.3