aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/video/video-abuse.ts
diff options
context:
space:
mode:
authorRigel Kent <sendmemail@rigelk.eu>2020-04-18 22:57:20 +0200
committerRigel Kent <par@rigelk.eu>2020-05-01 16:41:02 +0200
commit5fd4ca0051c7e7f3f8c47bcbde5cab0c56532e64 (patch)
tree1a62c391b834589532a074355bc218352b17b1e9 /server/models/video/video-abuse.ts
parent844db39ee56ff0dd59a96acfc68f10f9ac53000b (diff)
downloadPeerTube-5fd4ca0051c7e7f3f8c47bcbde5cab0c56532e64.tar.gz
PeerTube-5fd4ca0051c7e7f3f8c47bcbde5cab0c56532e64.tar.zst
PeerTube-5fd4ca0051c7e7f3f8c47bcbde5cab0c56532e64.zip
Add nth abuse count for a given video, add reporter/reportee reports stats
Diffstat (limited to 'server/models/video/video-abuse.ts')
-rw-r--r--server/models/video/video-abuse.ts96
1 files changed, 82 insertions, 14 deletions
diff --git a/server/models/video/video-abuse.ts b/server/models/video/video-abuse.ts
index 5ead02eca..d68608ca6 100644
--- a/server/models/video/video-abuse.ts
+++ b/server/models/video/video-abuse.ts
@@ -11,14 +11,13 @@ import {
11import { AccountModel } from '../account/account' 11import { AccountModel } from '../account/account'
12import { buildBlockedAccountSQL, getSort, throwIfNotValid } from '../utils' 12import { buildBlockedAccountSQL, getSort, throwIfNotValid } from '../utils'
13import { VideoModel } from './video' 13import { VideoModel } from './video'
14import { VideoAbuseState, Video } from '../../../shared' 14import { VideoAbuseState, VideoDetails } from '../../../shared'
15import { CONSTRAINTS_FIELDS, VIDEO_ABUSE_STATES } from '../../initializers/constants' 15import { CONSTRAINTS_FIELDS, VIDEO_ABUSE_STATES } from '../../initializers/constants'
16import { MUserAccountId, MVideoAbuse, MVideoAbuseFormattable, MVideoAbuseVideo } from '../../typings/models' 16import { MUserAccountId, MVideoAbuse, MVideoAbuseFormattable, MVideoAbuseVideo } from '../../typings/models'
17import * as Bluebird from 'bluebird' 17import * as Bluebird from 'bluebird'
18import { literal, Op } from 'sequelize' 18import { literal, Op } from 'sequelize'
19import { ThumbnailModel } from './thumbnail' 19import { ThumbnailModel } from './thumbnail'
20import { VideoChannelModel } from './video-channel' 20import { VideoChannelModel } from './video-channel'
21import { ActorModel } from '../activitypub/actor'
22import { VideoBlacklistModel } from './video-blacklist' 21import { VideoBlacklistModel } from './video-blacklist'
23 22
24export enum ScopeNames { 23export enum ScopeNames {
@@ -78,9 +77,73 @@ export enum ScopeNames {
78 }) 77 })
79 } 78 }
80 79
81 console.log(where)
82
83 return { 80 return {
81 attributes: {
82 include: [
83 [
84 literal(
85 '(' +
86 'SELECT t.count ' +
87 'FROM ( ' +
88 'SELECT id, ' +
89 'count(id) OVER (PARTITION BY "videoId") ' +
90 'FROM "videoAbuse" ' +
91 ') t ' +
92 'WHERE t.id = "VideoAbuseModel".id ' +
93 ')'
94 ),
95 'countReportsForVideo'
96 ],
97 [
98 literal(
99 '(' +
100 'SELECT t.nth ' +
101 'FROM ( ' +
102 'SELECT id, ' +
103 'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
104 'FROM "videoAbuse" ' +
105 ') t ' +
106 'WHERE t.id = "VideoAbuseModel".id ' +
107 ')'
108 ),
109 'nthReportForVideo'
110 ],
111 [
112 literal(
113 '(' +
114 'SELECT count("videoAbuse"."id") ' +
115 'FROM "videoAbuse" ' +
116 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
117 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
118 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
119 'WHERE "account"."id" = "VideoAbuseModel"."reporterAccountId" ' +
120 ')'
121 ),
122 'countReportsForReporter'
123 ],
124 [
125 literal(
126 '(' +
127 'WITH ' +
128 'ids AS ( ' +
129 'SELECT "account"."id" ' +
130 'FROM "account" ' +
131 'INNER JOIN "videoChannel" ON "videoChannel"."accountId" = "account"."id" ' +
132 'INNER JOIN "video" ON "video"."channelId" = "videoChannel"."id" ' +
133 'WHERE "video"."id" = "VideoAbuseModel"."videoId" ' +
134 ') ' +
135 'SELECT count("videoAbuse"."id") ' +
136 'FROM "videoAbuse" ' +
137 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
138 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
139 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
140 'INNER JOIN ids ON "account"."id" = ids.id ' +
141 ')'
142 ),
143 'countReportsForReportee'
144 ]
145 ]
146 },
84 include: [ 147 include: [
85 { 148 {
86 model: AccountModel, 149 model: AccountModel,
@@ -96,13 +159,8 @@ export enum ScopeNames {
96 model: ThumbnailModel 159 model: ThumbnailModel
97 }, 160 },
98 { 161 {
99 model: VideoChannelModel.unscoped(), 162 model: VideoChannelModel.scope([ 'WITH_ACTOR', 'WITH_ACCOUNT' ]),
100 where: { ...search(options.searchVideoChannel, 'name') }, 163 where: { ...search(options.searchVideoChannel, 'name') }
101 include: [
102 {
103 model: ActorModel
104 }
105 ]
106 }, 164 },
107 { 165 {
108 attributes: [ 'id', 'reason', 'unfederated' ], 166 attributes: [ 'id', 'reason', 'unfederated' ],
@@ -149,7 +207,7 @@ export class VideoAbuseModel extends Model<VideoAbuseModel> {
149 @AllowNull(true) 207 @AllowNull(true)
150 @Default(null) 208 @Default(null)
151 @Column(DataType.JSONB) 209 @Column(DataType.JSONB)
152 deletedVideo: Video 210 deletedVideo: VideoDetails
153 211
154 @CreatedAt 212 @CreatedAt
155 createdAt: Date 213 createdAt: Date
@@ -229,6 +287,11 @@ export class VideoAbuseModel extends Model<VideoAbuseModel> {
229 } 287 }
230 288
231 toFormattedJSON (this: MVideoAbuseFormattable): VideoAbuse { 289 toFormattedJSON (this: MVideoAbuseFormattable): VideoAbuse {
290 const countReportsForVideo = this.get('countReportsForVideo') as number
291 const nthReportForVideo = this.get('nthReportForVideo') as number
292 const countReportsForReporter = this.get('countReportsForReporter') as number
293 const countReportsForReportee = this.get('countReportsForReportee') as number
294
232 const video = this.Video 295 const video = this.Video
233 ? this.Video 296 ? this.Video
234 : this.deletedVideo 297 : this.deletedVideo
@@ -250,9 +313,14 @@ export class VideoAbuseModel extends Model<VideoAbuseModel> {
250 deleted: !this.Video, 313 deleted: !this.Video,
251 blacklisted: this.Video && this.Video.isBlacklisted(), 314 blacklisted: this.Video && this.Video.isBlacklisted(),
252 thumbnailPath: this.Video?.getMiniatureStaticPath(), 315 thumbnailPath: this.Video?.getMiniatureStaticPath(),
253 channel: this.Video?.VideoChannel.toFormattedSummaryJSON() || this.deletedVideo?.channel 316 channel: this.Video?.VideoChannel.toFormattedJSON() || this.deletedVideo?.channel
254 }, 317 },
255 createdAt: this.createdAt 318 createdAt: this.createdAt,
319 updatedAt: this.updatedAt,
320 count: countReportsForVideo || 0,
321 nth: nthReportForVideo || 0,
322 countReportsForReporter: countReportsForReporter || 0,
323 countReportsForReportee: countReportsForReportee || 0
256 } 324 }
257 } 325 }
258 326