server/models/abuse/sql/abuse-query-builder.ts

   1
   2 import { exists } from '@server/helpers/custom-validators/misc'
   3 import { forceNumber } from '@shared/core-utils'
   4 import { AbuseFilter, AbuseState, AbuseVideoIs } from '@shared/models'
   5 import { buildBlockedAccountSQL, buildSortDirectionAndField } from '../../shared'
   6
   7 export type BuildAbusesQueryOptions = {
   8   start: number
   9   count: number
  10   sort: string
  11
  12   // search
  13   search?: string
  14   searchReporter?: string
  15   searchReportee?: string
  16
  17   // video related
  18   searchVideo?: string
  19   searchVideoChannel?: string
  20   videoIs?: AbuseVideoIs
  21
  22   // filters
  23   id?: number
  24   predefinedReasonId?: number
  25   filter?: AbuseFilter
  26
  27   state?: AbuseState
  28
  29   // accountIds
  30   serverAccountId?: number
  31   userAccountId?: number
  32
  33   reporterAccountId?: number
  34 }
  35
  36 function buildAbuseListQuery (options: BuildAbusesQueryOptions, type: 'count' | 'id') {
  37   const whereAnd: string[] = []
  38   const replacements: any = {}
  39
  40   const joins = [
  41     'LEFT JOIN "videoAbuse" ON "videoAbuse"."abuseId" = "abuse"."id"',
  42     'LEFT JOIN "video" ON "videoAbuse"."videoId" = "video"."id"',
  43     'LEFT JOIN "videoBlacklist" ON "videoBlacklist"."videoId" = "video"."id"',
  44     'LEFT JOIN "videoChannel" ON "video"."channelId" = "videoChannel"."id"',
  45     'LEFT JOIN "account" "reporterAccount" ON "reporterAccount"."id" = "abuse"."reporterAccountId"',
  46     'LEFT JOIN "account" "flaggedAccount" ON "flaggedAccount"."id" = "abuse"."flaggedAccountId"',
  47     'LEFT JOIN "commentAbuse" ON "commentAbuse"."abuseId" = "abuse"."id"',
  48     'LEFT JOIN "videoComment" ON "commentAbuse"."videoCommentId" = "videoComment"."id"'
  49   ]
  50
  51   if (options.serverAccountId || options.userAccountId) {
  52     whereAnd.push(
  53       '"abuse"."reporterAccountId" IS NULL OR ' +
  54       '"abuse"."reporterAccountId" NOT IN (' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')'
  55     )
  56   }
  57
  58   if (options.reporterAccountId) {
  59     whereAnd.push('"abuse"."reporterAccountId" = :reporterAccountId')
  60     replacements.reporterAccountId = options.reporterAccountId
  61   }
  62
  63   if (options.search) {
  64     const searchWhereOr = [
  65       '"video"."name" ILIKE :search',
  66       '"videoChannel"."name" ILIKE :search',
  67       `"videoAbuse"."deletedVideo"->>'name' ILIKE :search`,
  68       `"videoAbuse"."deletedVideo"->'channel'->>'displayName' ILIKE :search`,
  69       '"reporterAccount"."name" ILIKE :search',
  70       '"flaggedAccount"."name" ILIKE :search'
  71     ]
  72
  73     replacements.search = `%${options.search}%`
  74     whereAnd.push('(' + searchWhereOr.join(' OR ') + ')')
  75   }
  76
  77   if (options.searchVideo) {
  78     whereAnd.push('"video"."name" ILIKE :searchVideo')
  79     replacements.searchVideo = `%${options.searchVideo}%`
  80   }
  81
  82   if (options.searchVideoChannel) {
  83     whereAnd.push('"videoChannel"."name" ILIKE :searchVideoChannel')
  84     replacements.searchVideoChannel = `%${options.searchVideoChannel}%`
  85   }
  86
  87   if (options.id) {
  88     whereAnd.push('"abuse"."id" = :id')
  89     replacements.id = options.id
  90   }
  91
  92   if (options.state) {
  93     whereAnd.push('"abuse"."state" = :state')
  94     replacements.state = options.state
  95   }
  96
  97   if (options.videoIs === 'deleted') {
  98     whereAnd.push('"videoAbuse"."deletedVideo" IS NOT NULL')
  99   } else if (options.videoIs === 'blacklisted') {
 100     whereAnd.push('"videoBlacklist"."id" IS NOT NULL')
 101   }
 102
 103   if (options.predefinedReasonId) {
 104     whereAnd.push(':predefinedReasonId = ANY("abuse"."predefinedReasons")')
 105     replacements.predefinedReasonId = options.predefinedReasonId
 106   }
 107
 108   if (options.filter === 'video') {
 109     whereAnd.push('"videoAbuse"."id" IS NOT NULL')
 110   } else if (options.filter === 'comment') {
 111     whereAnd.push('"commentAbuse"."id" IS NOT NULL')
 112   } else if (options.filter === 'account') {
 113     whereAnd.push('"videoAbuse"."id" IS NULL AND "commentAbuse"."id" IS NULL')
 114   }
 115
 116   if (options.searchReporter) {
 117     whereAnd.push('"reporterAccount"."name" ILIKE :searchReporter')
 118     replacements.searchReporter = `%${options.searchReporter}%`
 119   }
 120
 121   if (options.searchReportee) {
 122     whereAnd.push('"flaggedAccount"."name" ILIKE :searchReportee')
 123     replacements.searchReportee = `%${options.searchReportee}%`
 124   }
 125
 126   const prefix = type === 'count'
 127     ? 'SELECT COUNT("abuse"."id") AS "total"'
 128     : 'SELECT "abuse"."id" '
 129
 130   let suffix = ''
 131   if (type !== 'count') {
 132
 133     if (options.sort) {
 134       const order = buildAbuseOrder(options.sort)
 135       suffix += `${order} `
 136     }
 137
 138     if (exists(options.count)) {
 139       const count = forceNumber(options.count)
 140       suffix += `LIMIT ${count} `
 141     }
 142
 143     if (exists(options.start)) {
 144       const start = forceNumber(options.start)
 145       suffix += `OFFSET ${start} `
 146     }
 147   }
 148
 149   const where = whereAnd.length !== 0
 150     ? `WHERE ${whereAnd.join(' AND ')}`
 151     : ''
 152
 153   return {
 154     query: `${prefix} FROM "abuse" ${joins.join(' ')} ${where} ${suffix}`,
 155     replacements
 156   }
 157 }
 158
 159 function buildAbuseOrder (value: string) {
 160   const { direction, field } = buildSortDirectionAndField(value)
 161
 162   return `ORDER BY "abuse"."${field}" ${direction}`
 163 }
 164
 165 export {
 166   buildAbuseListQuery
 167 }