Fix video follow SQL

[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 285fb1fbc609a23b52915189f1235626c747a491..e0cf50b599811d487838212e39e0318490f0bd2e 100644 (file)
--- a/server/models/video/video-abuse.ts
+++ b/server/models/video/video-abuse.ts
@@ -1,6 +1,21 @@
+import * as Bluebird from 'bluebird'
+import { literal, Op } from 'sequelize'
 import {
-  AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, Is, Model, Table, UpdatedAt, Scopes
+  AllowNull,
+  BelongsTo,
+  Column,
+  CreatedAt,
+  DataType,
+  Default,
+  ForeignKey,
+  Is,
+  Model,
+  Scopes,
+  Table,
+  UpdatedAt
 } from 'sequelize-typescript'
+import { VideoAbuseVideoIs } from '@shared/models/videos/abuse/video-abuse-video-is.type'
+import { VideoAbuseState, VideoDetails } from '../../../shared'
 import { VideoAbuseObject } from '../../../shared/models/activitypub/objects'
 import { VideoAbuse } from '../../../shared/models/videos'
 import {
@@ -8,15 +23,12 @@ import {
   isVideoAbuseReasonValid,
   isVideoAbuseStateValid
 } from '../../helpers/custom-validators/video-abuses'
-import { AccountModel } from '../account/account'
-import { buildBlockedAccountSQL, getSort, throwIfNotValid, searchAttribute } from '../utils'
-import { VideoModel } from './video'
-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 { AccountModel } from '../account/account'
+import { buildBlockedAccountSQL, getSort, searchAttribute, throwIfNotValid } from '../utils'
 import { ThumbnailModel } from './thumbnail'
+import { VideoModel } from './video'
 import { VideoBlacklistModel } from './video-blacklist'
 import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
 
@@ -26,21 +38,31 @@ export enum ScopeNames {
 
 @Scopes(() => ({
   [ScopeNames.FOR_API]: (options: {
+    // search
     search?: string
     searchReporter?: string
+    searchReportee?: string
     searchVideo?: string
     searchVideoChannel?: string
+
+    // filters
+    id?: number
+
+    state?: VideoAbuseState
+    videoIs?: VideoAbuseVideoIs
+
+    // accountIds
     serverAccountId: number
     userAccountId: number
   }) => {
-    let where = {
+    const where = {
       reporterAccountId: {
-        [Op.notIn]: literal('(' + buildBlockedAccountSQL(options.serverAccountId, options.userAccountId) + ')')
+        [Op.notIn]: literal('(' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')')
       }
     }
 
     if (options.search) {
-      where = Object.assign(where, {
+      Object.assign(where, {
         [Op.or]: [
           {
             [Op.and]: [
@@ -71,6 +93,19 @@ export enum ScopeNames {
       })
     }
 
+    if (options.id) Object.assign(where, { id: options.id })
+    if (options.state) Object.assign(where, { state: options.state })
+
+    if (options.videoIs === 'deleted') {
+      Object.assign(where, {
+        deletedVideo: {
+          [Op.not]: null
+        }
+      })
+    }
+
+    const onlyBlacklisted = options.videoIs === 'blacklisted'
+
     return {
       attributes: {
         include: [
@@ -143,7 +178,8 @@ export enum ScopeNames {
                 '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) ` +
+                   `OR CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = ` +
+                      `CAST("VideoAbuseModel"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
               ')'
             ),
             'countReportsForReportee__deletedVideo'
@@ -158,7 +194,7 @@ export enum ScopeNames {
         },
         {
           model: VideoModel,
-          required: false,
+          required: !!(onlyBlacklisted || options.searchVideo || options.searchReportee || options.searchVideoChannel),
           where: searchAttribute(options.searchVideo, 'name'),
           include: [
             {
@@ -166,11 +202,18 @@ export enum ScopeNames {
             },
             {
               model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: true } as SummaryOptions ] }),
-              where: searchAttribute(options.searchVideoChannel, 'name')
+              where: searchAttribute(options.searchVideoChannel, 'name'),
+              include: [
+                {
+                  model: AccountModel,
+                  where: searchAttribute(options.searchReportee, 'name')
+                }
+              ]
             },
             {
               attributes: [ 'id', 'reason', 'unfederated' ],
-              model: VideoBlacklistModel
+              model: VideoBlacklistModel,
+              required: onlyBlacklisted
             }
           ]
         }
@@ -263,11 +306,36 @@ export class VideoAbuseModel extends Model<VideoAbuseModel> {
     start: number
     count: number
     sort: string
-    search?: string
+
     serverAccountId: number
     user?: MUserAccountId
+
+    id?: number
+    state?: VideoAbuseState
+    videoIs?: VideoAbuseVideoIs
+
+    search?: string
+    searchReporter?: string
+    searchReportee?: string
+    searchVideo?: string
+    searchVideoChannel?: string
   }) {
-    const { start, count, sort, search, user, serverAccountId } = parameters
+    const {
+      start,
+      count,
+      sort,
+      search,
+      user,
+      serverAccountId,
+      state,
+      videoIs,
+      searchReportee,
+      searchVideo,
+      searchVideoChannel,
+      searchReporter,
+      id
+    } = parameters
+
     const userAccountId = user ? user.Account.id : undefined
 
     const query = {
@@ -279,7 +347,14 @@ export class VideoAbuseModel extends Model<VideoAbuseModel> {
     }
 
     const filters = {
+      id,
       search,
+      state,
+      videoIs,
+      searchReportee,
+      searchVideo,
+      searchVideoChannel,
+      searchReporter,
       serverAccountId,
       userAccountId
     }