Use inner join and document code for viewr stats for channels

2 files changed, 35 insertions, 34 deletions

diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index 78fc3d7e4..642e129ff 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -166,42 +166,43 @@ export type SummaryOptions = {
       VideoModel
     ]
   },
-  [ScopeNames.WITH_STATS]: (options: AvailableWithStatsOptions = { daysPrior: 30 }) => ({
-    attributes: {
-      include: [
-        [
-          literal(
-            '(' +
-            `SELECT string_agg(concat_ws('|', t.day, t.views), ',') ` +
-            'FROM ( ' +
-              'WITH ' +
-                'days AS ( ' +
-                  `SELECT generate_series(date_trunc('day', now()) - '${options.daysPrior} day'::interval, ` +
-                         `date_trunc('day', now()), '1 day'::interval) AS day ` +
-                '), ' +
-                'views AS ( ' +
-                  'SELECT * ' +
-                  'FROM "videoView" ' +
-                  'WHERE "videoView"."videoId" IN ( ' +
-                    'SELECT "video"."id" ' +
-                    'FROM "video" ' +
+  [ScopeNames.WITH_STATS]: (options: AvailableWithStatsOptions = { daysPrior: 30 }) => {
+    const daysPrior = parseInt(options.daysPrior + '', 10)
+
+    return {
+      attributes: {
+        include: [
+          [
+            literal(
+              '(' +
+              `SELECT string_agg(concat_ws('|', t.day, t.views), ',') ` +
+              'FROM ( ' +
+                'WITH ' +
+                  '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 1 ' +
-              'ORDER BY day ' +
-            ') t' +
-            ')'
-          ),
-          'viewsPerDay'
+                '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' +
+              ')'
+            ),
+            'viewsPerDay'
+          ]
         ]
-      ]
+      }
     }
-  })
+  }
 }))
 @Table({
   tableName: 'videoChannel',
diff --git a/server/tests/api/videos/video-channels.ts b/server/tests/api/videos/video-channels.ts
index bde45584d..876a6ab66 100644
--- a/server/tests/api/videos/video-channels.ts
+++ b/server/tests/api/videos/video-channels.ts
@@ -2,7 +2,7 @@
 
 import * as chai from 'chai'
 import 'mocha'
-import { User, Video, VideoChannel, viewsPerTime, VideoDetails } from '../../../../shared/index'
+import { User, Video, VideoChannel, ViewsPerDate, VideoDetails } from '../../../../shared/index'
 import {
   cleanupTests,
   createUser,
@@ -376,7 +376,7 @@ describe('Test video channels', function () {
       res.body.data.forEach((channel: VideoChannel) => {
         expect(channel).to.haveOwnProperty('viewsPerDay')
         expect(channel.viewsPerDay).to.have.length(30 + 1) // daysPrior + today
-        channel.viewsPerDay.forEach((v: viewsPerTime) => {
+        channel.viewsPerDay.forEach((v: ViewsPerDate) => {
           expect(v.date).to.be.an('string')
           expect(v.views).to.equal(0)
         })