add channel and playlist stats to server stats endpoint (#3747)

[github/Chocobozzz/PeerTube.git] / server / models / video / video-channel.ts

diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index 815fb16c06901407aad3a021eee3af7c2fd12418..b7ffbd3b136f5bde0f2f55be3aec2e99f2e4dcd2 100644 (file)
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -1,4 +1,4 @@
-import { FindOptions, Includeable, literal, Op, ScopeOptions } from 'sequelize'
+import { FindOptions, Includeable, literal, Op, QueryTypes, ScopeOptions } from 'sequelize'
 import {
   AllowNull,
   BeforeDestroy,
@@ -28,10 +28,9 @@ import {
 import { CONSTRAINTS_FIELDS, WEBSERVER } from '../../initializers/constants'
 import { sendDeleteActor } from '../../lib/activitypub/send'
 import {
-  MChannelAccountDefault,
   MChannelActor,
-  MChannelActorAccountDefaultVideos,
   MChannelAP,
+  MChannelBannerAccountDefault,
   MChannelFormattable,
   MChannelSummaryFormattable
 } from '../../types/models/video'
@@ -49,6 +48,7 @@ export enum ScopeNames {
   SUMMARY = 'SUMMARY',
   WITH_ACCOUNT = 'WITH_ACCOUNT',
   WITH_ACTOR = 'WITH_ACTOR',
+  WITH_ACTOR_BANNER = 'WITH_ACTOR_BANNER',
   WITH_VIDEOS = 'WITH_VIDEOS',
   WITH_STATS = 'WITH_STATS'
 }
@@ -99,7 +99,14 @@ export type SummaryOptions = {
                 }
               }
             ]
-          }
+          },
+          include: [
+            {
+              model: ActorImageModel,
+              as: 'Banner',
+              required: false
+            }
+          ]
         },
         {
           model: AccountModel,
@@ -168,6 +175,20 @@ export type SummaryOptions = {
       ActorModel
     ]
   },
+  [ScopeNames.WITH_ACTOR_BANNER]: {
+    include: [
+      {
+        model: ActorModel,
+        include: [
+          {
+            model: ActorImageModel,
+            required: false,
+            as: 'Banner'
+          }
+        ]
+      }
+    ]
+  },
   [ScopeNames.WITH_VIDEOS]: {
     include: [
       VideoModel
@@ -317,6 +338,47 @@ export class VideoChannelModel extends Model {
     return VideoChannelModel.count(query)
   }
 
+  static async getStats () {
+
+    function getActiveVideoChannels (days: number) {
+      const options = {
+        type: QueryTypes.SELECT as QueryTypes.SELECT,
+        raw: true
+      }
+
+      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"`
+
+      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)
+
+    return {
+      totalLocalVideoChannels,
+      totalLocalDailyActiveVideoChannels,
+      totalLocalWeeklyActiveVideoChannels,
+      totalLocalMonthlyActiveVideoChannels,
+      totalHalfYearActiveVideoChannels
+    }
+  }
+
   static listForApi (parameters: {
     actorId: number
     start: number
@@ -442,7 +504,7 @@ export class VideoChannelModel extends Model {
       where
     }
 
-    const scopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR ]
+    const scopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR_BANNER ]
 
     if (options.withStats === true) {
       scopes.push({
@@ -458,32 +520,13 @@ export class VideoChannelModel extends Model {
       })
   }
 
-  static loadByIdAndPopulateAccount (id: number): Promise<MChannelAccountDefault> {
+  static loadAndPopulateAccount (id: number): Promise<MChannelBannerAccountDefault> {
     return VideoChannelModel.unscoped()
-      .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT ])
+      .scope([ ScopeNames.WITH_ACTOR_BANNER, ScopeNames.WITH_ACCOUNT ])
       .findByPk(id)
   }
 
-  static loadByIdAndAccount (id: number, accountId: number): Promise<MChannelAccountDefault> {
-    const query = {
-      where: {
-        id,
-        accountId
-      }
-    }
-
-    return VideoChannelModel.unscoped()
-      .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT ])
-      .findOne(query)
-  }
-
-  static loadAndPopulateAccount (id: number): Promise<MChannelAccountDefault> {
-    return VideoChannelModel.unscoped()
-      .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT ])
-      .findByPk(id)
-  }
-
-  static loadByUrlAndPopulateAccount (url: string): Promise<MChannelAccountDefault> {
+  static loadByUrlAndPopulateAccount (url: string): Promise<MChannelBannerAccountDefault> {
     const query = {
       include: [
         {
@@ -491,7 +534,14 @@ export class VideoChannelModel extends Model {
           required: true,
           where: {
             url
-          }
+          },
+          include: [
+            {
+              model: ActorImageModel,
+              required: false,
+              as: 'Banner'
+            }
+          ]
         }
       ]
     }
@@ -509,7 +559,7 @@ export class VideoChannelModel extends Model {
     return VideoChannelModel.loadByNameAndHostAndPopulateAccount(name, host)
   }
 
-  static loadLocalByNameAndPopulateAccount (name: string): Promise<MChannelAccountDefault> {
+  static loadLocalByNameAndPopulateAccount (name: string): Promise<MChannelBannerAccountDefault> {
     const query = {
       include: [
         {
@@ -518,17 +568,24 @@ export class VideoChannelModel extends Model {
           where: {
             preferredUsername: name,
             serverId: null
-          }
+          },
+          include: [
+            {
+              model: ActorImageModel,
+              required: false,
+              as: 'Banner'
+            }
+          ]
         }
       ]
     }
 
     return VideoChannelModel.unscoped()
-      .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT ])
+      .scope([ ScopeNames.WITH_ACCOUNT ])
       .findOne(query)
   }
 
-  static loadByNameAndHostAndPopulateAccount (name: string, host: string): Promise<MChannelAccountDefault> {
+  static loadByNameAndHostAndPopulateAccount (name: string, host: string): Promise<MChannelBannerAccountDefault> {
     const query = {
       include: [
         {
@@ -542,6 +599,11 @@ export class VideoChannelModel extends Model {
               model: ServerModel,
               required: true,
               where: { host }
+            },
+            {
+              model: ActorImageModel,
+              required: false,
+              as: 'Banner'
             }
           ]
         }
@@ -549,22 +611,10 @@ export class VideoChannelModel extends Model {
     }
 
     return VideoChannelModel.unscoped()
-      .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT ])
+      .scope([ ScopeNames.WITH_ACCOUNT ])
       .findOne(query)
   }
 
-  static loadAndPopulateAccountAndVideos (id: number): Promise<MChannelActorAccountDefaultVideos> {
-    const options = {
-      include: [
-        VideoModel
-      ]
-    }
-
-    return VideoChannelModel.unscoped()
-      .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_VIDEOS ])
-      .findByPk(id, options)
-  }
-
   toFormattedSummaryJSON (this: MChannelSummaryFormattable): VideoChannelSummary {
     const actor = this.Actor.toFormattedSummaryJSON()