aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/abuse/sql/abuse-query-builder.ts
diff options
context:
space:
mode:
Diffstat (limited to 'server/models/abuse/sql/abuse-query-builder.ts')
-rw-r--r--server/models/abuse/sql/abuse-query-builder.ts167
1 files changed, 167 insertions, 0 deletions
diff --git a/server/models/abuse/sql/abuse-query-builder.ts b/server/models/abuse/sql/abuse-query-builder.ts
new file mode 100644
index 000000000..282d4541a
--- /dev/null
+++ b/server/models/abuse/sql/abuse-query-builder.ts
@@ -0,0 +1,167 @@
1
2import { exists } from '@server/helpers/custom-validators/misc'
3import { forceNumber } from '@shared/core-utils'
4import { AbuseFilter, AbuseState, AbuseVideoIs } from '@shared/models'
5import { buildBlockedAccountSQL, buildSortDirectionAndField } from '../../shared'
6
7export 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
36function 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
159function buildAbuseOrder (value: string) {
160 const { direction, field } = buildSortDirectionAndField(value)
161
162 return `ORDER BY "abuse"."${field}" ${direction}`
163}
164
165export {
166 buildAbuseListQuery
167}