From 8b60488020883c66d3831eacd030893ab184268f Mon Sep 17 00:00:00 2001
From: Chocobozzz <me@florianbigard.com>
Date: Tue, 28 Aug 2018 18:29:29 +0200
Subject: Refractor user quota SQL queries

---
 server/models/account/user.ts        | 73 +++++++++++++++++-------------------
 server/models/video/video-channel.ts |  2 +-
 2 files changed, 36 insertions(+), 39 deletions(-)

(limited to 'server/models')

diff --git a/server/models/account/user.ts b/server/models/account/user.ts
index a88ec244f..bae683b12 100644
--- a/server/models/account/user.ts
+++ b/server/models/account/user.ts
@@ -172,8 +172,8 @@ export class UserModel extends Model<UserModel> {
           [
             Sequelize.literal(
               '(' +
-                'SELECT COALESCE(SUM("size"), 0) FROM ' +
-                '(' +
+                'SELECT COALESCE(SUM("size"), 0) ' +
+                'FROM (' +
                   'SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' +
                   'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
                   'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
@@ -267,48 +267,17 @@ export class UserModel extends Model<UserModel> {
 
   static getOriginalVideoFileTotalFromUser (user: UserModel) {
     // Don't use sequelize because we need to use a sub query
-    const query = 'SELECT SUM("size") AS "total" FROM ' +
-      '(SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' +
-      'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
-      'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
-      'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
-      'WHERE "account"."userId" = $userId ' +
-      'GROUP BY "video"."id") t'
+    const query = UserModel.generateUserQuotaBaseSQL()
 
-    const options = {
-      bind: { userId: user.id },
-      type: Sequelize.QueryTypes.SELECT
-    }
-    return UserModel.sequelize.query(query, options)
-      .then(([ { total } ]) => {
-        if (total === null) return 0
-
-        return parseInt(total, 10)
-      })
+    return UserModel.getTotalRawQuery(query, user.id)
   }
 
-  // Returns comulative size of all video files uploaded in the last 24 hours.
+  // Returns cumulative size of all video files uploaded in the last 24 hours.
   static getOriginalVideoFileTotalDailyFromUser (user: UserModel) {
     // Don't use sequelize because we need to use a sub query
-    const query = 'SELECT SUM("size") AS "total" FROM ' +
-      '(SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' +
-      'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
-      'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
-      'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
-      'WHERE "account"."userId" = $userId ' +
-      'AND "video"."createdAt" > now() - interval \'24 hours\'' +
-      'GROUP BY "video"."id") t'
-
-    const options = {
-      bind: { userId: user.id },
-      type: Sequelize.QueryTypes.SELECT
-    }
-    return UserModel.sequelize.query(query, options)
-      .then(([ { total } ]) => {
-        if (total === null) return 0
+    const query = UserModel.generateUserQuotaBaseSQL('"video"."createdAt" > now() - interval \'24 hours\'')
 
-        return parseInt(total, 10)
-      })
+    return UserModel.getTotalRawQuery(query, user.id)
   }
 
   static async getStats () {
@@ -388,4 +357,32 @@ export class UserModel extends Model<UserModel> {
     return (uploadedTotal < this.videoQuota) &&
         (uploadedDaily < this.videoQuotaDaily)
   }
+
+  private static generateUserQuotaBaseSQL (where?: string) {
+    const andWhere = where ? 'AND ' + where : ''
+
+    return 'SELECT SUM("size") AS "total" ' +
+      'FROM (' +
+        'SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' +
+        'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
+        'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
+        'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
+        'WHERE "account"."userId" = $userId ' + andWhere +
+        'GROUP BY "video"."id"' +
+      ') t'
+  }
+
+  private static getTotalRawQuery (query: string, userId: number) {
+    const options = {
+      bind: { userId },
+      type: Sequelize.QueryTypes.SELECT
+    }
+
+    return UserModel.sequelize.query(query, options)
+                    .then(([ { total } ]) => {
+                      if (total === null) return 0
+
+                      return parseInt(total, 10)
+                    })
+  }
 }
diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index e70e52515..475530daf 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -71,7 +71,7 @@ type AvailableForListOptions = {
     const inQueryInstanceFollow = '(' +
       'SELECT "actor"."serverId" FROM "actorFollow" ' +
       'INNER JOIN "actor" ON actor.id=  "actorFollow"."targetActorId" ' +
-      'WHERE "actor"."id" = ' + actorIdNumber +
+      'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
     ')'
 
     return {
-- 
cgit v1.2.3