X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=server%2Fmodels%2Fabuse%2Fabuse.ts;h=de249d21144454a6a81bd8062f9ea627eb525f20;hb=b49f22d8f9a52ab75fd38db2d377249eb58fa678;hp=4f99f9c9b836e7a6955fa0f2229995f4fc8096b6;hpb=d95d15598847c7f020aa056e7e6e0c02d2bbf732;p=github%2FChocobozzz%2FPeerTube.git

diff --git a/server/models/abuse/abuse.ts b/server/models/abuse/abuse.ts
index 4f99f9c9b..de249d211 100644
--- a/server/models/abuse/abuse.ts
+++ b/server/models/abuse/abuse.ts
@@ -1,6 +1,5 @@
-import * as Bluebird from 'bluebird'
 import { invert } from 'lodash'
-import { literal, Op, WhereOptions } from 'sequelize'
+import { literal, Op, QueryTypes } from 'sequelize'
 import {
   AllowNull,
   BelongsTo,
@@ -17,25 +16,30 @@ import {
   UpdatedAt
 } from 'sequelize-typescript'
 import { isAbuseModerationCommentValid, isAbuseReasonValid, isAbuseStateValid } from '@server/helpers/custom-validators/abuses'
+import { abusePredefinedReasonsMap } from '@shared/core-utils/abuse'
 import {
-  Abuse,
+  AbuseFilter,
   AbuseObject,
   AbusePredefinedReasons,
-  abusePredefinedReasonsMap,
   AbusePredefinedReasonsString,
   AbuseState,
   AbuseVideoIs,
-  VideoAbuse
+  AdminAbuse,
+  AdminVideoAbuse,
+  AdminVideoCommentAbuse,
+  UserAbuse,
+  UserVideoAbuse
 } from '@shared/models'
-import { AbuseFilter } from '@shared/models/moderation/abuse/abuse-filter'
-import { CONSTRAINTS_FIELDS, ABUSE_STATES } from '../../initializers/constants'
-import { MAbuse, MAbuseAP, MAbuseFormattable, MUserAccountId } from '../../types/models'
-import { AccountModel, ScopeNames as AccountScopeNames } from '../account/account'
-import { buildBlockedAccountSQL, getSort, searchAttribute, throwIfNotValid } from '../utils'
+import { ABUSE_STATES, CONSTRAINTS_FIELDS } from '../../initializers/constants'
+import { MAbuseAdminFormattable, MAbuseAP, MAbuseFull, MAbuseReporter, MAbuseUserFormattable, MUserAccountId } from '../../types/models'
+import { AccountModel, ScopeNames as AccountScopeNames, SummaryOptions as AccountSummaryOptions } from '../account/account'
+import { getSort, throwIfNotValid } from '../utils'
 import { ThumbnailModel } from '../video/thumbnail'
-import { VideoModel } from '../video/video'
+import { ScopeNames as VideoScopeNames, VideoModel } from '../video/video'
 import { VideoBlacklistModel } from '../video/video-blacklist'
-import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from '../video/video-channel'
+import { ScopeNames as VideoChannelScopeNames, SummaryOptions as ChannelSummaryOptions, VideoChannelModel } from '../video/video-channel'
+import { ScopeNames as CommentScopeNames, VideoCommentModel } from '../video/video-comment'
+import { buildAbuseListQuery, BuildAbusesQueryOptions } from './abuse-query-builder'
 import { VideoAbuseModel } from './video-abuse'
 import { VideoCommentAbuseModel } from './video-comment-abuse'
 
@@ -44,101 +48,27 @@ export enum ScopeNames {
 }
 
 @Scopes(() => ({
-  [ScopeNames.FOR_API]: (options: {
-    // search
-    search?: string
-    searchReporter?: string
-    searchReportee?: string
-
-    // video releated
-    searchVideo?: string
-    searchVideoChannel?: string
-    videoIs?: AbuseVideoIs
-
-    // filters
-    id?: number
-    predefinedReasonId?: number
-    filter?: AbuseFilter
-
-    state?: AbuseState
-
-    // accountIds
-    serverAccountId: number
-    userAccountId: number
-  }) => {
-    const onlyBlacklisted = options.videoIs === 'blacklisted'
-    const videoRequired = !!(onlyBlacklisted || options.searchVideo || options.searchVideoChannel)
-
-    const where = {
-      reporterAccountId: {
-        [Op.notIn]: literal('(' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')')
-      }
-    }
-
-    if (options.search) {
-      const escapedSearch = AbuseModel.sequelize.escape('%' + options.search + '%')
-
-      Object.assign(where, {
-        [Op.or]: [
-          {
-            [Op.and]: [
-              { '$VideoAbuse.videoId$': { [Op.not]: null } },
-              searchAttribute(options.search, '$VideoAbuse.Video.name$')
-            ]
-          },
-          {
-            [Op.and]: [
-              { '$VideoAbuse.videoId$': { [Op.not]: null } },
-              searchAttribute(options.search, '$VideoAbuse.Video.VideoChannel.name$')
-            ]
-          },
-          {
-            [Op.and]: [
-              { '$VideoAbuse.deletedVideo$': { [Op.not]: null } },
-              literal(`"VideoAbuse"."deletedVideo"->>'name' ILIKE ${escapedSearch}`)
-            ]
-          },
-          {
-            [Op.and]: [
-              { '$VideoAbuse.deletedVideo$': { [Op.not]: null } },
-              literal(`"VideoAbuse"."deletedVideo"->'channel'->>'displayName' ILIKE ${escapedSearch}`)
-            ]
-          },
-          searchAttribute(options.search, '$ReporterAccount.name$'),
-          searchAttribute(options.search, '$FlaggedAccount.name$')
-        ]
-      })
-    }
-
-    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, {
-        '$VideoAbuse.deletedVideo$': {
-          [Op.not]: null
-        }
-      })
-    }
-
-    if (options.predefinedReasonId) {
-      Object.assign(where, {
-        predefinedReasons: {
-          [Op.contains]: [ options.predefinedReasonId ]
-        }
-      })
-    }
-
+  [ScopeNames.FOR_API]: () => {
     return {
       attributes: {
         include: [
+          [
+            literal(
+              '(' +
+                'SELECT count(*) ' +
+                'FROM "abuseMessage" ' +
+                'WHERE "abuseId" = "AbuseModel"."id"' +
+              ')'
+            ),
+            'countMessages'
+          ],
           [
             // we don't care about this count for deleted videos, so there are not included
             literal(
               '(' +
                 'SELECT count(*) ' +
                 'FROM "videoAbuse" ' +
-                'WHERE "videoId" = "VideoAbuse"."videoId" ' +
+                'WHERE "videoId" = "VideoAbuse"."videoId" AND "videoId" IS NOT NULL' +
               ')'
             ),
             'countReportsForVideo'
@@ -153,7 +83,7 @@ export enum ScopeNames {
                          'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
                   'FROM "videoAbuse" ' +
                 ') t ' +
-                'WHERE t.id = "VideoAbuse".id' +
+                'WHERE t.id = "VideoAbuse".id AND t.id IS NOT NULL' +
               ')'
             ),
             'nthReportForVideo'
@@ -161,102 +91,88 @@ export enum ScopeNames {
           [
             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" = "AbuseModel"."reporterAccountId" ' +
+                'SELECT count("abuse"."id") ' +
+                'FROM "abuse" ' +
+                'WHERE "abuse"."reporterAccountId" = "AbuseModel"."reporterAccountId"' +
               ')'
             ),
-            'countReportsForReporter__video'
+            'countReportsForReporter'
           ],
           [
             literal(
               '(' +
-                'SELECT count(DISTINCT "videoAbuse"."id") ' +
-                'FROM "videoAbuse" ' +
-                `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "AbuseModel"."reporterAccountId" ` +
+                'SELECT count("abuse"."id") ' +
+                'FROM "abuse" ' +
+                'WHERE "abuse"."flaggedAccountId" = "AbuseModel"."flaggedAccountId"' +
               ')'
             ),
-            '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" = "VideoAbuse->Video->VideoChannel"."accountId" ' +
-                   `OR "videoChannel"."accountId" = CAST("VideoAbuse"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
-              ')'
-            ),
-            'countReportsForReportee__video'
-          ],
-          [
-            literal(
-              '(' +
-                'SELECT count(DISTINCT "videoAbuse"."id") ' +
-                'FROM "videoAbuse" ' +
-                `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "VideoAbuse->Video->VideoChannel"."accountId" ` +
-                   `OR CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = ` +
-                      `CAST("VideoAbuse"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
-              ')'
-            ),
-            'countReportsForReportee__deletedVideo'
+            'countReportsForReportee'
           ]
         ]
       },
       include: [
         {
-          model: AccountModel.scope(AccountScopeNames.SUMMARY),
-          as: 'ReporterAccount',
-          required: true,
-          where: searchAttribute(options.searchReporter, 'name')
+          model: AccountModel.scope({
+            method: [
+              AccountScopeNames.SUMMARY,
+              { actorRequired: false } as AccountSummaryOptions
+            ]
+          }),
+          as: 'ReporterAccount'
         },
         {
-          model: AccountModel.scope(AccountScopeNames.SUMMARY),
-          as: 'FlaggedAccount',
-          required: true,
-          where: searchAttribute(options.searchReportee, 'name')
+          model: AccountModel.scope({
+            method: [
+              AccountScopeNames.SUMMARY,
+              { actorRequired: false } as AccountSummaryOptions
+            ]
+          }),
+          as: 'FlaggedAccount'
         },
         {
-          model: VideoAbuseModel,
-          required: options.filter === 'video' || !!options.videoIs || videoRequired,
+          model: VideoCommentAbuseModel.unscoped(),
+          include: [
+            {
+              model: VideoCommentModel.unscoped(),
+              include: [
+                {
+                  model: VideoModel.unscoped(),
+                  attributes: [ 'name', 'id', 'uuid' ]
+                }
+              ]
+            }
+          ]
+        },
+        {
+          model: VideoAbuseModel.unscoped(),
           include: [
             {
-              model: VideoModel,
-              required: videoRequired,
-              where: searchAttribute(options.searchVideo, 'name'),
+              attributes: [ 'id', 'uuid', 'name', 'nsfw' ],
+              model: VideoModel.unscoped(),
               include: [
                 {
+                  attributes: [ 'filename', 'fileUrl', 'type' ],
                   model: ThumbnailModel
                 },
                 {
-                  model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: false } as SummaryOptions ] }),
-                  where: searchAttribute(options.searchVideoChannel, 'name'),
-                  required: true,
-                  include: [
-                    {
-                      model: AccountModel.scope(AccountScopeNames.SUMMARY),
-                      required: true,
-                      where: searchAttribute(options.searchReportee, 'name')
-                    }
-                  ]
+                  model: VideoChannelModel.scope({
+                    method: [
+                      VideoChannelScopeNames.SUMMARY,
+                      { withAccount: false, actorRequired: false } as ChannelSummaryOptions
+                    ]
+                  }),
+                  required: false
                 },
                 {
                   attributes: [ 'id', 'reason', 'unfederated' ],
-                  model: VideoBlacklistModel,
-                  required: onlyBlacklisted
+                  required: false,
+                  model: VideoBlacklistModel
                 }
               ]
             }
           ]
         }
-      ],
-      where
+      ]
     }
   }
 }))
@@ -271,23 +187,23 @@ export enum ScopeNames {
     }
   ]
 })
-export class AbuseModel extends Model<AbuseModel> {
+export class AbuseModel extends Model {
 
   @AllowNull(false)
   @Default(null)
-  @Is('VideoAbuseReason', value => throwIfNotValid(value, isAbuseReasonValid, 'reason'))
+  @Is('AbuseReason', value => throwIfNotValid(value, isAbuseReasonValid, 'reason'))
   @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.REASON.max))
   reason: string
 
   @AllowNull(false)
   @Default(null)
-  @Is('VideoAbuseState', value => throwIfNotValid(value, isAbuseStateValid, 'state'))
+  @Is('AbuseState', value => throwIfNotValid(value, isAbuseStateValid, 'state'))
   @Column
   state: AbuseState
 
   @AllowNull(true)
   @Default(null)
-  @Is('VideoAbuseModerationComment', value => throwIfNotValid(value, isAbuseModerationCommentValid, 'moderationComment', true))
+  @Is('AbuseModerationComment', value => throwIfNotValid(value, isAbuseModerationCommentValid, 'moderationComment', true))
   @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.MODERATION_COMMENT.max))
   moderationComment: string
 
@@ -348,28 +264,69 @@ export class AbuseModel extends Model<AbuseModel> {
   })
   VideoAbuse: VideoAbuseModel
 
-  static loadByIdAndVideoId (id: number, videoId?: number, uuid?: string): Bluebird<MAbuse> {
-    const videoWhere: WhereOptions = {}
+  static loadByIdWithReporter (id: number): Promise<MAbuseReporter> {
+    const query = {
+      where: {
+        id
+      },
+      include: [
+        {
+          model: AccountModel,
+          as: 'ReporterAccount'
+        }
+      ]
+    }
 
-    if (videoId) videoWhere.videoId = videoId
-    if (uuid) videoWhere.deletedVideo = { uuid }
+    return AbuseModel.findOne(query)
+  }
 
+  static loadFull (id: number): Promise<MAbuseFull> {
     const query = {
+      where: {
+        id
+      },
       include: [
+        {
+          model: AccountModel.scope(AccountScopeNames.SUMMARY),
+          required: false,
+          as: 'ReporterAccount'
+        },
+        {
+          model: AccountModel.scope(AccountScopeNames.SUMMARY),
+          as: 'FlaggedAccount'
+        },
         {
           model: VideoAbuseModel,
-          required: true,
-          where: videoWhere
+          required: false,
+          include: [
+            {
+              model: VideoModel.scope([ VideoScopeNames.WITH_ACCOUNT_DETAILS ])
+            }
+          ]
+        },
+        {
+          model: VideoCommentAbuseModel,
+          required: false,
+          include: [
+            {
+              model: VideoCommentModel.scope([
+                CommentScopeNames.WITH_ACCOUNT
+              ]),
+              include: [
+                {
+                  model: VideoModel
+                }
+              ]
+            }
+          ]
         }
-      ],
-      where: {
-        id
-      }
+      ]
     }
+
     return AbuseModel.findOne(query)
   }
 
-  static listForApi (parameters: {
+  static async listForAdminApi (parameters: {
     start: number
     count: number
     sort: string
@@ -411,15 +368,10 @@ export class AbuseModel extends Model<AbuseModel> {
     const userAccountId = user ? user.Account.id : undefined
     const predefinedReasonId = predefinedReason ? abusePredefinedReasonsMap[predefinedReason] : undefined
 
-    const query = {
-      offset: start,
-      limit: count,
-      order: getSort(sort),
-      col: 'AbuseModel.id',
-      distinct: true
-    }
-
-    const filters = {
+    const queryOptions: BuildAbusesQueryOptions = {
+      start,
+      count,
+      sort,
       id,
       filter,
       predefinedReasonId,
@@ -434,77 +386,171 @@ export class AbuseModel extends Model<AbuseModel> {
       userAccountId
     }
 
-    return AbuseModel
-      .scope([
-        { method: [ ScopeNames.FOR_API, filters ] }
-      ])
-      .findAndCountAll(query)
-      .then(({ rows, count }) => {
-        return { total: count, data: rows }
-      })
+    const [ total, data ] = await Promise.all([
+      AbuseModel.internalCountForApi(queryOptions),
+      AbuseModel.internalListForApi(queryOptions)
+    ])
+
+    return { total, data }
   }
 
-  toFormattedJSON (this: MAbuseFormattable): Abuse {
-    const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
-    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
-
-    let video: VideoAbuse
-
-    if (this.VideoAbuse) {
-      const abuseModel = this.VideoAbuse
-      const entity = abuseModel.Video || abuseModel.deletedVideo
-
-      video = {
-        id: entity.id,
-        uuid: entity.uuid,
-        name: entity.name,
-        nsfw: entity.nsfw,
-
-        startAt: abuseModel.startAt,
-        endAt: abuseModel.endAt,
-
-        deleted: !abuseModel.Video,
-        blacklisted: abuseModel.Video?.isBlacklisted() || false,
-        thumbnailPath: abuseModel.Video?.getMiniatureStaticPath(),
-        channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel
+  static async listForUserApi (parameters: {
+    user: MUserAccountId
+
+    start: number
+    count: number
+    sort: string
+
+    id?: number
+    search?: string
+    state?: AbuseState
+  }) {
+    const {
+      start,
+      count,
+      sort,
+      search,
+      user,
+      state,
+      id
+    } = parameters
+
+    const queryOptions: BuildAbusesQueryOptions = {
+      start,
+      count,
+      sort,
+      id,
+      search,
+      state,
+      reporterAccountId: user.Account.id
+    }
+
+    const [ total, data ] = await Promise.all([
+      AbuseModel.internalCountForApi(queryOptions),
+      AbuseModel.internalListForApi(queryOptions)
+    ])
+
+    return { total, data }
+  }
+
+  buildBaseVideoCommentAbuse (this: MAbuseUserFormattable) {
+    if (!this.VideoCommentAbuse) return null
+
+    const abuseModel = this.VideoCommentAbuse
+    const entity = abuseModel.VideoComment
+
+    return {
+      id: entity.id,
+      threadId: entity.getThreadId(),
+
+      text: entity.text ?? '',
+
+      deleted: entity.isDeleted(),
+
+      video: {
+        id: entity.Video.id,
+        name: entity.Video.name,
+        uuid: entity.Video.uuid
       }
     }
+  }
+
+  buildBaseVideoAbuse (this: MAbuseUserFormattable): UserVideoAbuse {
+    if (!this.VideoAbuse) return null
+
+    const abuseModel = this.VideoAbuse
+    const entity = abuseModel.Video || abuseModel.deletedVideo
+
+    return {
+      id: entity.id,
+      uuid: entity.uuid,
+      name: entity.name,
+      nsfw: entity.nsfw,
+
+      startAt: abuseModel.startAt,
+      endAt: abuseModel.endAt,
+
+      deleted: !abuseModel.Video,
+      blacklisted: abuseModel.Video?.isBlacklisted() || false,
+      thumbnailPath: abuseModel.Video?.getMiniatureStaticPath(),
+
+      channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel
+    }
+  }
+
+  buildBaseAbuse (this: MAbuseUserFormattable, countMessages: number): UserAbuse {
+    const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
 
     return {
       id: this.id,
       reason: this.reason,
       predefinedReasons,
 
-      reporterAccount: this.ReporterAccount.toFormattedJSON(),
+      flaggedAccount: this.FlaggedAccount
+        ? this.FlaggedAccount.toFormattedJSON()
+        : null,
 
       state: {
         id: this.state,
         label: AbuseModel.getStateLabel(this.state)
       },
 
-      moderationComment: this.moderationComment,
-
-      video,
-      comment: null,
+      countMessages,
 
       createdAt: this.createdAt,
-      updatedAt: this.updatedAt,
-      count: countReportsForVideo || 0,
-      nth: nthReportForVideo || 0,
-      countReportsForReporter: (countReportsForReporterVideo || 0) + (countReportsForReporterDeletedVideo || 0),
-      countReportsForReportee: (countReportsForReporteeVideo || 0) + (countReportsForReporteeDeletedVideo || 0),
-
-      // FIXME: deprecated in 2.3, remove this
-      startAt: null,
-      endAt: null
+      updatedAt: this.updatedAt
     }
   }
 
+  toFormattedAdminJSON (this: MAbuseAdminFormattable): AdminAbuse {
+    const countReportsForVideo = this.get('countReportsForVideo') as number
+    const nthReportForVideo = this.get('nthReportForVideo') as number
+
+    const countReportsForReporter = this.get('countReportsForReporter') as number
+    const countReportsForReportee = this.get('countReportsForReportee') as number
+
+    const countMessages = this.get('countMessages') as number
+
+    const baseVideo = this.buildBaseVideoAbuse()
+    const video: AdminVideoAbuse = baseVideo
+      ? Object.assign(baseVideo, {
+        countReports: countReportsForVideo,
+        nthReport: nthReportForVideo
+      })
+      : null
+
+    const comment: AdminVideoCommentAbuse = this.buildBaseVideoCommentAbuse()
+
+    const abuse = this.buildBaseAbuse(countMessages || 0)
+
+    return Object.assign(abuse, {
+      video,
+      comment,
+
+      moderationComment: this.moderationComment,
+
+      reporterAccount: this.ReporterAccount
+        ? this.ReporterAccount.toFormattedJSON()
+        : null,
+
+      countReportsForReporter: (countReportsForReporter || 0),
+      countReportsForReportee: (countReportsForReportee || 0)
+    })
+  }
+
+  toFormattedUserJSON (this: MAbuseUserFormattable): UserAbuse {
+    const countMessages = this.get('countMessages') as number
+
+    const video = this.buildBaseVideoAbuse()
+    const comment = this.buildBaseVideoCommentAbuse()
+    const abuse = this.buildBaseAbuse(countMessages || 0)
+
+    return Object.assign(abuse, {
+      video,
+      comment
+    })
+  }
+
   toActivityPubObject (this: MAbuseAP): AbuseObject {
     const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
 
@@ -526,13 +572,51 @@ export class AbuseModel extends Model<AbuseModel> {
     }
   }
 
+  private static async internalCountForApi (parameters: BuildAbusesQueryOptions) {
+    const { query, replacements } = buildAbuseListQuery(parameters, 'count')
+    const options = {
+      type: QueryTypes.SELECT as QueryTypes.SELECT,
+      replacements
+    }
+
+    const [ { total } ] = await AbuseModel.sequelize.query<{ total: string }>(query, options)
+    if (total === null) return 0
+
+    return parseInt(total, 10)
+  }
+
+  private static async internalListForApi (parameters: BuildAbusesQueryOptions) {
+    const { query, replacements } = buildAbuseListQuery(parameters, 'id')
+    const options = {
+      type: QueryTypes.SELECT as QueryTypes.SELECT,
+      replacements
+    }
+
+    const rows = await AbuseModel.sequelize.query<{ id: string }>(query, options)
+    const ids = rows.map(r => r.id)
+
+    if (ids.length === 0) return []
+
+    return AbuseModel.scope(ScopeNames.FOR_API)
+                     .findAll({
+                       order: getSort(parameters.sort),
+                       where: {
+                         id: {
+                           [Op.in]: ids
+                         }
+                       }
+                     })
+  }
+
   private static getStateLabel (id: number) {
     return ABUSE_STATES[id] || 'Unknown'
   }
 
   private static getPredefinedReasonsStrings (predefinedReasons: AbusePredefinedReasons[]): AbusePredefinedReasonsString[] {
+    const invertedPredefinedReasons = invert(abusePredefinedReasonsMap)
+
     return (predefinedReasons || [])
-      .filter(r => r in AbusePredefinedReasons)
-      .map(r => invert(abusePredefinedReasonsMap)[r] as AbusePredefinedReasonsString)
+      .map(r => invertedPredefinedReasons[r] as AbusePredefinedReasonsString)
+      .filter(v => !!v)
   }
 }