From 5a61ffbb7c72dd7ecfa16c7944dac45411c1bbe9 Mon Sep 17 00:00:00 2001
From: Chocobozzz <me@florianbigard.com>
Date: Fri, 12 Jun 2020 16:01:42 +0200
Subject: [PATCH] Optimize views per day in video channels

---
 .../validators/videos/video-channels.ts       | 17 +++++++-----
 server/models/video/video-channel.ts          | 26 ++++++++-----------
 server/tests/api/videos/video-channels.ts     |  1 +
 3 files changed, 22 insertions(+), 22 deletions(-)

diff --git a/server/middlewares/validators/videos/video-channels.ts b/server/middlewares/validators/videos/video-channels.ts
index 882fb2b84..6604ae6a8 100644
--- a/server/middlewares/validators/videos/video-channels.ts
+++ b/server/middlewares/validators/videos/video-channels.ts
@@ -1,20 +1,20 @@
 import * as express from 'express'
 import { body, param, query } from 'express-validator'
+import { VIDEO_CHANNELS } from '@server/initializers/constants'
+import { MChannelAccountDefault, MUser } from '@server/typings/models'
 import { UserRight } from '../../../../shared'
+import { isActorPreferredUsernameValid } from '../../../helpers/custom-validators/activitypub/actor'
+import { isBooleanValid, toBooleanOrNull } from '../../../helpers/custom-validators/misc'
 import {
   isVideoChannelDescriptionValid,
   isVideoChannelNameValid,
   isVideoChannelSupportValid
 } from '../../../helpers/custom-validators/video-channels'
 import { logger } from '../../../helpers/logger'
+import { doesLocalVideoChannelNameExist, doesVideoChannelNameWithHostExist } from '../../../helpers/middlewares'
+import { ActorModel } from '../../../models/activitypub/actor'
 import { VideoChannelModel } from '../../../models/video/video-channel'
 import { areValidationErrors } from '../utils'
-import { isActorPreferredUsernameValid } from '../../../helpers/custom-validators/activitypub/actor'
-import { ActorModel } from '../../../models/activitypub/actor'
-import { isBooleanValid } from '../../../helpers/custom-validators/misc'
-import { doesLocalVideoChannelNameExist, doesVideoChannelNameWithHostExist } from '../../../helpers/middlewares'
-import { MChannelAccountDefault, MUser } from '@server/typings/models'
-import { VIDEO_CHANNELS } from '@server/initializers/constants'
 
 const videoChannelsAddValidator = [
   body('name').custom(isActorPreferredUsernameValid).withMessage('Should have a valid channel name'),
@@ -129,7 +129,10 @@ const localVideoChannelValidator = [
 ]
 
 const videoChannelStatsValidator = [
-  query('withStats').optional().isBoolean().withMessage('Should have a valid stats flag'),
+  query('withStats')
+    .optional()
+    .customSanitizer(toBooleanOrNull)
+    .custom(isBooleanValid).withMessage('Should have a valid stats flag'),
 
   (req: express.Request, res: express.Response, next: express.NextFunction) => {
     if (areValidationErrors(req, res)) return
diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index 642e129ff..b5bcbdc65 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -181,20 +181,16 @@ export type SummaryOptions = {
                   'days AS ( ' +
                     `SELECT generate_series(date_trunc('day', now()) - '${daysPrior} day'::interval, ` +
                           `date_trunc('day', now()), '1 day'::interval) AS day ` +
-                  '), ' +
-                  'views AS ( ' +
-                    'SELECT v.* ' +
-                    'FROM "videoView" AS v ' +
-                    'INNER JOIN "video" ON "video"."id" = v."videoId" ' +
-                    'WHERE "video"."channelId" = "VideoChannelModel"."id" ' +
                   ') ' +
-                'SELECT days.day AS day, ' +
-                      'COALESCE(SUM(views.views), 0) AS views ' +
-                'FROM days ' +
-                `LEFT JOIN views ON date_trunc('day', "views"."startDate") = date_trunc('day', days.day) ` +
-                'GROUP BY day ' +
-                'ORDER BY day ' +
-              ') t' +
+                  'SELECT days.day AS day, COALESCE(SUM("videoView".views), 0) AS views ' +
+                  'FROM days ' +
+                  'LEFT JOIN (' +
+                    '"videoView" INNER JOIN "video" ON "videoView"."videoId" = "video"."id" ' +
+                    'AND "video"."channelId" = "VideoChannelModel"."id"' +
+                  `) ON date_trunc('day', "videoView"."startDate") = date_trunc('day', days.day) ` +
+                  'GROUP BY day ' +
+                  'ORDER BY day ' +
+                ') t' +
               ')'
             ),
             'viewsPerDay'
@@ -413,7 +409,7 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
 
     const scopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR ]
 
-    if (options.withStats) {
+    if (options.withStats === true) {
       scopes.push({
         method: [ ScopeNames.WITH_STATS, { daysPrior: 30 } as AvailableWithStatsOptions ]
       })
@@ -560,7 +556,7 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
       createdAt: this.createdAt,
       updatedAt: this.updatedAt,
       ownerAccount: undefined,
-      viewsPerDay: viewsPerDay !== undefined
+      viewsPerDay: viewsPerDay
         ? viewsPerDay.split(',').map(v => {
           const o = v.split('|')
           return {
diff --git a/server/tests/api/videos/video-channels.ts b/server/tests/api/videos/video-channels.ts
index 876a6ab66..89026aba7 100644
--- a/server/tests/api/videos/video-channels.ts
+++ b/server/tests/api/videos/video-channels.ts
@@ -366,6 +366,7 @@ describe('Test video channels', function () {
   })
 
   it('Should report correct channel statistics', async function () {
+    this.timeout(10000)
 
     {
       const res = await getAccountVideoChannelsList({
-- 
2.25.1