+ if (options.id) Object.assign(where, { id: options.id })
+ if (options.state) Object.assign(where, { state: options.state })
+
+ if (options.videoIs === 'deleted') {
+ Object.assign(where, {
+ deletedVideo: {
+ [Op.not]: null
+ }
+ })
+ }
+
+ if (options.predefinedReasonId) {
+ Object.assign(where, {
+ predefinedReasons: {
+ [Op.contains]: [ options.predefinedReasonId ]
+ }
+ })
+ }
+
+ const onlyBlacklisted = options.videoIs === 'blacklisted'
+
+ return {
+ attributes: {
+ include: [
+ [
+ // we don't care about this count for deleted videos, so there are not included
+ literal(
+ '(' +
+ 'SELECT count(*) ' +
+ 'FROM "videoAbuse" ' +
+ 'WHERE "videoId" = "VideoAbuseModel"."videoId" ' +
+ ')'
+ ),
+ 'countReportsForVideo'
+ ],
+ [
+ // we don't care about this count for deleted videos, so there are not included
+ literal(
+ '(' +
+ 'SELECT t.nth ' +
+ 'FROM ( ' +
+ 'SELECT id, ' +
+ 'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
+ 'FROM "videoAbuse" ' +
+ ') t ' +
+ 'WHERE t.id = "VideoAbuseModel".id ' +
+ ')'
+ ),
+ 'nthReportForVideo'
+ ],
+ [
+ literal(
+ '(' +
+ 'SELECT count("videoAbuse"."id") ' +
+ 'FROM "videoAbuse" ' +
+ 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
+ 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
+ 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
+ 'WHERE "account"."id" = "VideoAbuseModel"."reporterAccountId" ' +
+ ')'
+ ),
+ 'countReportsForReporter__video'
+ ],
+ [
+ literal(
+ '(' +
+ 'SELECT count(DISTINCT "videoAbuse"."id") ' +
+ 'FROM "videoAbuse" ' +
+ `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "VideoAbuseModel"."reporterAccountId" ` +
+ ')'
+ ),
+ 'countReportsForReporter__deletedVideo'
+ ],
+ [
+ literal(
+ '(' +
+ 'SELECT count(DISTINCT "videoAbuse"."id") ' +
+ 'FROM "videoAbuse" ' +
+ 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
+ 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
+ 'INNER JOIN "account" ON ' +
+ '"videoChannel"."accountId" = "Video->VideoChannel"."accountId" ' +
+ `OR "videoChannel"."accountId" = CAST("VideoAbuseModel"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
+ ')'
+ ),
+ 'countReportsForReportee__video'
+ ],
+ [
+ literal(
+ '(' +
+ 'SELECT count(DISTINCT "videoAbuse"."id") ' +
+ 'FROM "videoAbuse" ' +
+ `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "Video->VideoChannel"."accountId" ` +
+ `OR CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = ` +
+ `CAST("VideoAbuseModel"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
+ ')'
+ ),
+ 'countReportsForReportee__deletedVideo'
+ ]
+ ]
+ },
+ include: [
+ {
+ model: AccountModel,
+ required: true,
+ where: searchAttribute(options.searchReporter, 'name')
+ },
+ {
+ model: VideoModel,
+ required: !!(onlyBlacklisted || options.searchVideo || options.searchReportee || options.searchVideoChannel),
+ where: searchAttribute(options.searchVideo, 'name'),
+ include: [
+ {
+ model: ThumbnailModel
+ },
+ {
+ model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: true } as SummaryOptions ] }),
+ where: searchAttribute(options.searchVideoChannel, 'name'),
+ include: [
+ {
+ model: AccountModel,
+ where: searchAttribute(options.searchReportee, 'name')
+ }
+ ]
+ },
+ {
+ attributes: [ 'id', 'reason', 'unfederated' ],
+ model: VideoBlacklistModel,
+ required: onlyBlacklisted
+ }
+ ]
+ }
+ ],
+ where
+ }
+ }
+}))