From dfa4944f3401b5c1225e3235a016510c9f5a7c8a Mon Sep 17 00:00:00 2001
From: Chocobozzz <me@florianbigard.com>
Date: Wed, 21 Dec 2022 10:46:55 +0100
Subject: Fix local channel stats

---
 server/models/video/video-channel.ts | 37 ++++++++++++++++++------------------
 1 file changed, 18 insertions(+), 19 deletions(-)

(limited to 'server/models')

diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index 9e461b6ca..132c8f021 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -434,42 +434,41 @@ export class VideoChannelModel extends Model<Partial<AttributesOnly<VideoChannel
 
   static async getStats () {
 
-    function getActiveVideoChannels (days: number) {
+    function getLocalVideoChannelStats (days?: number) {
       const options = {
         type: QueryTypes.SELECT as QueryTypes.SELECT,
         raw: true
       }
 
+      const videoJoin = days
+        ? `INNER JOIN "video" AS "Videos" ON "VideoChannelModel"."id" = "Videos"."channelId" ` +
+             `AND ("Videos"."publishedAt" > Now() - interval '${days}d')`
+        : ''
+
       const query = `
-SELECT          COUNT(DISTINCT("VideoChannelModel"."id")) AS "count"
-FROM            "videoChannel"                            AS "VideoChannelModel"
-INNER JOIN      "video"                                   AS "Videos"
-ON              "VideoChannelModel"."id" = "Videos"."channelId"
-AND             ("Videos"."publishedAt" > Now() - interval '${days}d')
-INNER JOIN      "account" AS "Account"
-ON              "VideoChannelModel"."accountId" = "Account"."id"
-INNER JOIN      "actor" AS "Account->Actor"
-ON              "Account"."actorId" = "Account->Actor"."id"
-AND             "Account->Actor"."serverId" IS NULL
-LEFT OUTER JOIN "server" AS "Account->Actor->Server"
-ON              "Account->Actor"."serverId" = "Account->Actor->Server"."id"`
+      SELECT COUNT(DISTINCT("VideoChannelModel"."id")) AS "count"
+      FROM "videoChannel" AS "VideoChannelModel"
+      ${videoJoin}
+      INNER JOIN "account" AS "Account" ON "VideoChannelModel"."accountId" = "Account"."id"
+      INNER JOIN "actor" AS "Account->Actor" ON "Account"."actorId" = "Account->Actor"."id"
+        AND "Account->Actor"."serverId" IS NULL`
 
       return VideoChannelModel.sequelize.query<{ count: string }>(query, options)
                               .then(r => parseInt(r[0].count, 10))
     }
 
-    const totalLocalVideoChannels = await VideoChannelModel.count()
-    const totalLocalDailyActiveVideoChannels = await getActiveVideoChannels(1)
-    const totalLocalWeeklyActiveVideoChannels = await getActiveVideoChannels(7)
-    const totalLocalMonthlyActiveVideoChannels = await getActiveVideoChannels(30)
-    const totalHalfYearActiveVideoChannels = await getActiveVideoChannels(180)
+    const totalLocalVideoChannels = await getLocalVideoChannelStats()
+    const totalLocalDailyActiveVideoChannels = await getLocalVideoChannelStats(1)
+    const totalLocalWeeklyActiveVideoChannels = await getLocalVideoChannelStats(7)
+    const totalLocalMonthlyActiveVideoChannels = await getLocalVideoChannelStats(30)
+    const totalLocalHalfYearActiveVideoChannels = await getLocalVideoChannelStats(180)
 
     return {
       totalLocalVideoChannels,
       totalLocalDailyActiveVideoChannels,
       totalLocalWeeklyActiveVideoChannels,
       totalLocalMonthlyActiveVideoChannels,
-      totalHalfYearActiveVideoChannels
+      totalLocalHalfYearActiveVideoChannels
     }
   }
 
-- 
cgit v1.2.3