Add reportee stats for deleted videos

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

diff --git a/server/models/video/video-abuse.ts b/server/models/video/video-abuse.ts
index 5ead02ecae5d46b446b1a18d2be99e2848ddac7e..285fb1fbc609a23b52915189f1235626c747a491 100644 (file)
--- a/server/models/video/video-abuse.ts
+++ b/server/models/video/video-abuse.ts
@@ -9,17 +9,16 @@ import {
   isVideoAbuseStateValid
 } from '../../helpers/custom-validators/video-abuses'
 import { AccountModel } from '../account/account'
-import { buildBlockedAccountSQL, getSort, throwIfNotValid } from '../utils'
+import { buildBlockedAccountSQL, getSort, throwIfNotValid, searchAttribute } from '../utils'
 import { VideoModel } from './video'
-import { VideoAbuseState, Video } from '../../../shared'
+import { VideoAbuseState, VideoDetails } from '../../../shared'
 import { CONSTRAINTS_FIELDS, VIDEO_ABUSE_STATES } from '../../initializers/constants'
 import { MUserAccountId, MVideoAbuse, MVideoAbuseFormattable, MVideoAbuseVideo } from '../../typings/models'
 import * as Bluebird from 'bluebird'
 import { literal, Op } from 'sequelize'
 import { ThumbnailModel } from './thumbnail'
-import { VideoChannelModel } from './video-channel'
-import { ActorModel } from '../activitypub/actor'
 import { VideoBlacklistModel } from './video-blacklist'
+import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
 
 export enum ScopeNames {
   FOR_API = 'FOR_API'
@@ -32,14 +31,8 @@ export enum ScopeNames {
     searchVideo?: string
     searchVideoChannel?: string
     serverAccountId: number
-    userAccountId: any
+    userAccountId: number
   }) => {
-    const search = (sourceField, targetField) => sourceField ? ({
-      [targetField]: {
-        [Op.iLike]: `%${sourceField}%`
-      }
-    }) : {}
-
     let where = {
       reporterAccountId: {
         [Op.notIn]: literal('(' + buildBlockedAccountSQL(options.serverAccountId, options.userAccountId) + ')')
@@ -52,57 +45,128 @@ export enum ScopeNames {
           {
             [Op.and]: [
               { videoId: { [Op.not]: null } },
-              { '$Video.name$': { [Op.iLike]: `%${options.search}%` } }
+              searchAttribute(options.search, '$Video.name$')
             ]
           },
           {
             [Op.and]: [
               { videoId: { [Op.not]: null } },
-              { '$Video.VideoChannel.name$': { [Op.iLike]: `%${options.search}%` } }
+              searchAttribute(options.search, '$Video.VideoChannel.name$')
             ]
           },
           {
             [Op.and]: [
               { deletedVideo: { [Op.not]: null } },
-              { deletedVideo: { name: { [Op.iLike]: `%${options.search}%` } } }
+              { deletedVideo: searchAttribute(options.search, 'name') }
             ]
           },
           {
             [Op.and]: [
               { deletedVideo: { [Op.not]: null } },
-              { deletedVideo: { channel: { displayName: { [Op.iLike]: `%${options.search}%` } } } }
+              { deletedVideo: { channel: searchAttribute(options.search, 'displayName') } }
             ]
           },
-          { '$Account.name$': { [Op.iLike]: `%${options.search}%` } }
+          searchAttribute(options.search, '$Account.name$')
         ]
       })
     }
 
-    console.log(where)
-
     return {
+      attributes: {
+        include: [
+          [
+            // we don't care about this count for deleted videos, so there are not included
+            literal(
+              '(' +
+                'SELECT count(*) ' +
+                'FROM "videoAbuse" ' +
+                'WHERE "videoId" = "VideoAbuseModel"."videoId" ' +
+              ')'
+            ),
+            'countReportsForVideo'
+          ],
+          [
+            // we don't care about this count for deleted videos, so there are not included
+            literal(
+              '(' +
+                'SELECT t.nth ' +
+                'FROM ( ' +
+                  'SELECT id, ' +
+                         'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
+                  'FROM "videoAbuse" ' +
+                ') t ' +
+                'WHERE t.id = "VideoAbuseModel".id ' +
+              ')'
+            ),
+            'nthReportForVideo'
+          ],
+          [
+            literal(
+              '(' +
+                'SELECT count("videoAbuse"."id") ' +
+                'FROM "videoAbuse" ' +
+                'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
+                'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
+                'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
+                'WHERE "account"."id" = "VideoAbuseModel"."reporterAccountId" ' +
+              ')'
+            ),
+            'countReportsForReporter__video'
+          ],
+          [
+            literal(
+              '(' +
+                'SELECT count(DISTINCT "videoAbuse"."id") ' +
+                'FROM "videoAbuse" ' +
+                `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "VideoAbuseModel"."reporterAccountId" ` +
+              ')'
+            ),
+            'countReportsForReporter__deletedVideo'
+          ],
+          [
+            literal(
+              '(' +
+                'SELECT count(DISTINCT "videoAbuse"."id") ' +
+                'FROM "videoAbuse" ' +
+                'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
+                'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
+                'INNER JOIN "account" ON ' +
+                      '"videoChannel"."accountId" = "Video->VideoChannel"."accountId" ' +
+                   `OR "videoChannel"."accountId" = CAST("VideoAbuseModel"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
+              ')'
+            ),
+            'countReportsForReportee__video'
+          ],
+          [
+            literal(
+              '(' +
+                'SELECT count(DISTINCT "videoAbuse"."id") ' +
+                'FROM "videoAbuse" ' +
+                `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "Video->VideoChannel"."accountId" ` +
+                   `OR CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = CAST("VideoAbuseModel"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
+              ')'
+            ),
+            'countReportsForReportee__deletedVideo'
+          ]
+        ]
+      },
       include: [
         {
           model: AccountModel,
           required: true,
-          where: { ...search(options.searchReporter, 'name') }
+          where: searchAttribute(options.searchReporter, 'name')
         },
         {
           model: VideoModel,
           required: false,
-          where: { ...search(options.searchVideo, 'name') },
+          where: searchAttribute(options.searchVideo, 'name'),
           include: [
             {
               model: ThumbnailModel
             },
             {
-              model: VideoChannelModel.unscoped(),
-              where: { ...search(options.searchVideoChannel, 'name') },
-              include: [
-                {
-                  model: ActorModel
-                }
-              ]
+              model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: true } as SummaryOptions ] }),
+              where: searchAttribute(options.searchVideoChannel, 'name')
             },
             {
               attributes: [ 'id', 'reason', 'unfederated' ],
@@ -149,7 +213,7 @@ export class VideoAbuseModel extends Model<VideoAbuseModel> {
   @AllowNull(true)
   @Default(null)
   @Column(DataType.JSONB)
-  deletedVideo: Video
+  deletedVideo: VideoDetails
 
   @CreatedAt
   createdAt: Date
@@ -229,6 +293,13 @@ export class VideoAbuseModel extends Model<VideoAbuseModel> {
   }
 
   toFormattedJSON (this: MVideoAbuseFormattable): VideoAbuse {
+    const countReportsForVideo = this.get('countReportsForVideo') as number
+    const nthReportForVideo = this.get('nthReportForVideo') as number
+    const countReportsForReporterVideo = this.get('countReportsForReporter__video') as number
+    const countReportsForReporterDeletedVideo = this.get('countReportsForReporter__deletedVideo') as number
+    const countReportsForReporteeVideo = this.get('countReportsForReportee__video') as number
+    const countReportsForReporteeDeletedVideo = this.get('countReportsForReportee__deletedVideo') as number
+
     const video = this.Video
       ? this.Video
       : this.deletedVideo
@@ -250,9 +321,14 @@ export class VideoAbuseModel extends Model<VideoAbuseModel> {
         deleted: !this.Video,
         blacklisted: this.Video && this.Video.isBlacklisted(),
         thumbnailPath: this.Video?.getMiniatureStaticPath(),
-        channel: this.Video?.VideoChannel.toFormattedSummaryJSON() || this.deletedVideo?.channel
+        channel: this.Video?.VideoChannel.toFormattedJSON() || this.deletedVideo?.channel
       },
-      createdAt: this.createdAt
+      createdAt: this.createdAt,
+      updatedAt: this.updatedAt,
+      count: countReportsForVideo || 0,
+      nth: nthReportForVideo || 0,
+      countReportsForReporter: (countReportsForReporterVideo || 0) + (countReportsForReporterDeletedVideo || 0),
+      countReportsForReportee: (countReportsForReporteeVideo || 0) + (countReportsForReporteeDeletedVideo || 0)
     }
   }