aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/abuse/abuse-query-builder.ts
diff options
context:
space:
mode:
Diffstat (limited to 'server/models/abuse/abuse-query-builder.ts')
-rw-r--r--server/models/abuse/abuse-query-builder.ts154
1 files changed, 154 insertions, 0 deletions
diff --git a/server/models/abuse/abuse-query-builder.ts b/server/models/abuse/abuse-query-builder.ts
new file mode 100644
index 000000000..5fddcf3c4
--- /dev/null
+++ b/server/models/abuse/abuse-query-builder.ts
@@ -0,0 +1,154 @@
1
2import { exists } from '@server/helpers/custom-validators/misc'
3import { AbuseFilter, AbuseState, AbuseVideoIs } from '@shared/models'
4import { buildBlockedAccountSQL, buildDirectionAndField } from '../utils'
5
6export 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
33function 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
146function buildAbuseOrder (value: string) {
147 const { direction, field } = buildDirectionAndField(value)
148
149 return `ORDER BY "abuse"."${field}" ${direction}`
150}
151
152export {
153 buildAbuseListQuery
154}