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

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