Factorize rest-table and fix/simplify SQL

3 files changed, 18 insertions, 33 deletions

diff --git a/server/models/video/video-abuse.ts b/server/models/video/video-abuse.ts
index e8c3bd823..71b519cd9 100644
--- a/server/models/video/video-abuse.ts
+++ b/server/models/video/video-abuse.ts
@@ -15,7 +15,7 @@ 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 { literal, Op, Sequelize } from 'sequelize'
 import { ThumbnailModel } from './thumbnail'
 import { VideoBlacklistModel } from './video-blacklist'
 import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
@@ -31,7 +31,7 @@ export enum ScopeNames {
     searchVideo?: string
     searchVideoChannel?: string
     serverAccountId: number
-    userAccountId: any
+    userAccountId: number
   }) => {
     let where = {
       reporterAccountId: {
@@ -45,28 +45,28 @@ 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$')
         ]
       })
     }
@@ -77,13 +77,9 @@ export enum ScopeNames {
           [
             literal(
               '(' +
-                'SELECT t.count ' +
-                'FROM ( ' +
-                  'SELECT id, ' +
-                         'count(id) OVER (PARTITION BY "videoId") ' +
-                  'FROM "videoAbuse" ' +
-                ') t ' +
-                'WHERE t.id = "VideoAbuseModel".id ' +
+                'SELECT count(*) ' +
+                'FROM "videoAbuse" ' +
+                'WHERE "videoId" = "VideoAbuseModel"."videoId" ' +
               ')'
             ),
             'countReportsForVideo'
@@ -118,20 +114,11 @@ export enum ScopeNames {
           [
             literal(
               '(' +
-                'WITH ' +
-                  'ids AS ( ' +
-                    'SELECT "account"."id" ' +
-                    'FROM "account" ' +
-                    'INNER JOIN "videoChannel" ON "videoChannel"."accountId" = "account"."id" ' +
-                    'INNER JOIN "video" ON "video"."channelId" = "videoChannel"."id" ' +
-                    'WHERE "video"."id" = "VideoAbuseModel"."videoId" ' +
-                  ') ' +
-                'SELECT count("videoAbuse"."id") ' +
+                '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" = "account"."id" ' +
-                'INNER JOIN ids ON "account"."id" = ids.id ' +
+                'INNER JOIN "account" ON "videoChannel"."accountId" = "Video->VideoChannel"."accountId" ' +
               ')'
             ),
             'countReportsForReportee'
@@ -142,19 +129,19 @@ export enum ScopeNames {
         {
           model: AccountModel,
           required: true,
-          where: { ...searchAttribute(options.searchReporter, 'name') }
+          where: searchAttribute(options.searchReporter, 'name')
         },
         {
           model: VideoModel,
           required: false,
-          where: { ...searchAttribute(options.searchVideo, 'name') },
+          where: searchAttribute(options.searchVideo, 'name'),
           include: [
             {
               model: ThumbnailModel
             },
             {
               model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: true } as SummaryOptions ] }),
-              where: { ...searchAttribute(options.searchVideoChannel, 'name') }
+              where: searchAttribute(options.searchVideoChannel, 'name')
             },
             {
               attributes: [ 'id', 'reason', 'unfederated' ],
diff --git a/server/models/video/video-blacklist.ts b/server/models/video/video-blacklist.ts
index 680eba471..8cbfe362e 100644
--- a/server/models/video/video-blacklist.ts
+++ b/server/models/video/video-blacklist.ts
@@ -78,7 +78,7 @@ export class VideoBlacklistModel extends Model<VideoBlacklistModel> {
       {
         model: VideoModel,
         required: true,
-        where: { ...searchAttribute(search, 'name') },
+        where: searchAttribute(search, 'name'),
         include: [
           {
             model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: true } as SummaryOptions ] }),
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index f32216e90..ccb9d64ca 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -813,10 +813,8 @@ export class VideoModel extends Model<VideoModel> {
     const details = instance.toFormattedDetailsJSON()
 
     for (const abuse of instance.VideoAbuses) {
-      tasks.push((_ => {
-        abuse.deletedVideo = details
-        return abuse.save({ transaction: options.transaction })
-      })())
+      abuse.deletedVideo = details
+      tasks.push(abuse.save({ transaction: options.transaction }))
     }
 
     Promise.all(tasks)