server/models/user/sql/user-notitication-list-query-builder.ts

   1 import { Sequelize } from 'sequelize'
   2 import { AbstractRunQuery, ModelBuilder } from '@server/models/shared'
   3 import { UserNotificationModelForApi } from '@server/types/models'
   4 import { ActorImageType } from '@shared/models'
   5 import { getSort } from '../../shared'
   6
   7 export interface ListNotificationsOptions {
   8   userId: number
   9   unread?: boolean
  10   sort: string
  11   offset: number
  12   limit: number
  13 }
  14
  15 export class UserNotificationListQueryBuilder extends AbstractRunQuery {
  16   private innerQuery: string
  17
  18   constructor (
  19     protected readonly sequelize: Sequelize,
  20     private readonly options: ListNotificationsOptions
  21   ) {
  22     super(sequelize)
  23   }
  24
  25   async listNotifications () {
  26     this.buildQuery()
  27
  28     const results = await this.runQuery({ nest: true })
  29     const modelBuilder = new ModelBuilder<UserNotificationModelForApi>(this.sequelize)
  30
  31     return modelBuilder.createModels(results, 'UserNotification')
  32   }
  33
  34   private buildInnerQuery () {
  35     this.innerQuery = `SELECT * FROM "userNotification" AS "UserNotificationModel" ` +
  36       `${this.getWhere()} ` +
  37       `${this.getOrder()} ` +
  38       `LIMIT :limit OFFSET :offset `
  39
  40     this.replacements.limit = this.options.limit
  41     this.replacements.offset = this.options.offset
  42   }
  43
  44   private buildQuery () {
  45     this.buildInnerQuery()
  46
  47     this.query = `
  48       ${this.getSelect()}
  49       FROM (${this.innerQuery}) "UserNotificationModel"
  50       ${this.getJoins()}
  51       ${this.getOrder()}`
  52   }
  53
  54   private getWhere () {
  55     let base = '"UserNotificationModel"."userId" = :userId '
  56     this.replacements.userId = this.options.userId
  57
  58     if (this.options.unread === true) {
  59       base += 'AND "UserNotificationModel"."read" IS FALSE '
  60     } else if (this.options.unread === false) {
  61       base += 'AND "UserNotificationModel"."read" IS TRUE '
  62     }
  63
  64     return `WHERE ${base}`
  65   }
  66
  67   private getOrder () {
  68     const orders = getSort(this.options.sort)
  69
  70     return 'ORDER BY ' + orders.map(o => `"UserNotificationModel"."${o[0]}" ${o[1]}`).join(', ')
  71   }
  72
  73   private getSelect () {
  74     return `SELECT
  75       "UserNotificationModel"."id",
  76       "UserNotificationModel"."type",
  77       "UserNotificationModel"."read",
  78       "UserNotificationModel"."createdAt",
  79       "UserNotificationModel"."updatedAt",
  80       "Video"."id" AS "Video.id",
  81       "Video"."uuid" AS "Video.uuid",
  82       "Video"."name" AS "Video.name",
  83       "Video->VideoChannel"."id" AS "Video.VideoChannel.id",
  84       "Video->VideoChannel"."name" AS "Video.VideoChannel.name",
  85       "Video->VideoChannel->Actor"."id" AS "Video.VideoChannel.Actor.id",
  86       "Video->VideoChannel->Actor"."preferredUsername" AS "Video.VideoChannel.Actor.preferredUsername",
  87       "Video->VideoChannel->Actor->Avatars"."id" AS "Video.VideoChannel.Actor.Avatars.id",
  88       "Video->VideoChannel->Actor->Avatars"."width" AS "Video.VideoChannel.Actor.Avatars.width",
  89       "Video->VideoChannel->Actor->Avatars"."type" AS "Video.VideoChannel.Actor.Avatars.type",
  90       "Video->VideoChannel->Actor->Avatars"."filename" AS "Video.VideoChannel.Actor.Avatars.filename",
  91       "Video->VideoChannel->Actor->Server"."id" AS "Video.VideoChannel.Actor.Server.id",
  92       "Video->VideoChannel->Actor->Server"."host" AS "Video.VideoChannel.Actor.Server.host",
  93       "VideoComment"."id" AS "VideoComment.id",
  94       "VideoComment"."originCommentId" AS "VideoComment.originCommentId",
  95       "VideoComment->Account"."id" AS "VideoComment.Account.id",
  96       "VideoComment->Account"."name" AS "VideoComment.Account.name",
  97       "VideoComment->Account->Actor"."id" AS "VideoComment.Account.Actor.id",
  98       "VideoComment->Account->Actor"."preferredUsername" AS "VideoComment.Account.Actor.preferredUsername",
  99       "VideoComment->Account->Actor->Avatars"."id" AS "VideoComment.Account.Actor.Avatars.id",
 100       "VideoComment->Account->Actor->Avatars"."width" AS "VideoComment.Account.Actor.Avatars.width",
 101       "VideoComment->Account->Actor->Avatars"."type" AS "VideoComment.Account.Actor.Avatars.type",
 102       "VideoComment->Account->Actor->Avatars"."filename" AS "VideoComment.Account.Actor.Avatars.filename",
 103       "VideoComment->Account->Actor->Server"."id" AS "VideoComment.Account.Actor.Server.id",
 104       "VideoComment->Account->Actor->Server"."host" AS "VideoComment.Account.Actor.Server.host",
 105       "VideoComment->Video"."id" AS "VideoComment.Video.id",
 106       "VideoComment->Video"."uuid" AS "VideoComment.Video.uuid",
 107       "VideoComment->Video"."name" AS "VideoComment.Video.name",
 108       "Abuse"."id" AS "Abuse.id",
 109       "Abuse"."state" AS "Abuse.state",
 110       "Abuse->VideoAbuse"."id" AS "Abuse.VideoAbuse.id",
 111       "Abuse->VideoAbuse->Video"."id" AS "Abuse.VideoAbuse.Video.id",
 112       "Abuse->VideoAbuse->Video"."uuid" AS "Abuse.VideoAbuse.Video.uuid",
 113       "Abuse->VideoAbuse->Video"."name" AS "Abuse.VideoAbuse.Video.name",
 114       "Abuse->VideoCommentAbuse"."id" AS "Abuse.VideoCommentAbuse.id",
 115       "Abuse->VideoCommentAbuse->VideoComment"."id" AS "Abuse.VideoCommentAbuse.VideoComment.id",
 116       "Abuse->VideoCommentAbuse->VideoComment"."originCommentId" AS "Abuse.VideoCommentAbuse.VideoComment.originCommentId",
 117       "Abuse->VideoCommentAbuse->VideoComment->Video"."id" AS "Abuse.VideoCommentAbuse.VideoComment.Video.id",
 118       "Abuse->VideoCommentAbuse->VideoComment->Video"."name" AS "Abuse.VideoCommentAbuse.VideoComment.Video.name",
 119       "Abuse->VideoCommentAbuse->VideoComment->Video"."uuid" AS "Abuse.VideoCommentAbuse.VideoComment.Video.uuid",
 120       "Abuse->FlaggedAccount"."id" AS "Abuse.FlaggedAccount.id",
 121       "Abuse->FlaggedAccount"."name" AS "Abuse.FlaggedAccount.name",
 122       "Abuse->FlaggedAccount"."description" AS "Abuse.FlaggedAccount.description",
 123       "Abuse->FlaggedAccount"."actorId" AS "Abuse.FlaggedAccount.actorId",
 124       "Abuse->FlaggedAccount"."userId" AS "Abuse.FlaggedAccount.userId",
 125       "Abuse->FlaggedAccount"."applicationId" AS "Abuse.FlaggedAccount.applicationId",
 126       "Abuse->FlaggedAccount"."createdAt" AS "Abuse.FlaggedAccount.createdAt",
 127       "Abuse->FlaggedAccount"."updatedAt" AS "Abuse.FlaggedAccount.updatedAt",
 128       "Abuse->FlaggedAccount->Actor"."id" AS "Abuse.FlaggedAccount.Actor.id",
 129       "Abuse->FlaggedAccount->Actor"."preferredUsername" AS "Abuse.FlaggedAccount.Actor.preferredUsername",
 130       "Abuse->FlaggedAccount->Actor->Avatars"."id" AS "Abuse.FlaggedAccount.Actor.Avatars.id",
 131       "Abuse->FlaggedAccount->Actor->Avatars"."width" AS "Abuse.FlaggedAccount.Actor.Avatars.width",
 132       "Abuse->FlaggedAccount->Actor->Avatars"."type" AS "Abuse.FlaggedAccount.Actor.Avatars.type",
 133       "Abuse->FlaggedAccount->Actor->Avatars"."filename" AS "Abuse.FlaggedAccount.Actor.Avatars.filename",
 134       "Abuse->FlaggedAccount->Actor->Server"."id" AS "Abuse.FlaggedAccount.Actor.Server.id",
 135       "Abuse->FlaggedAccount->Actor->Server"."host" AS "Abuse.FlaggedAccount.Actor.Server.host",
 136       "VideoBlacklist"."id" AS "VideoBlacklist.id",
 137       "VideoBlacklist->Video"."id" AS "VideoBlacklist.Video.id",
 138       "VideoBlacklist->Video"."uuid" AS "VideoBlacklist.Video.uuid",
 139       "VideoBlacklist->Video"."name" AS "VideoBlacklist.Video.name",
 140       "VideoImport"."id" AS "VideoImport.id",
 141       "VideoImport"."magnetUri" AS "VideoImport.magnetUri",
 142       "VideoImport"."targetUrl" AS "VideoImport.targetUrl",
 143       "VideoImport"."torrentName" AS "VideoImport.torrentName",
 144       "VideoImport->Video"."id" AS "VideoImport.Video.id",
 145       "VideoImport->Video"."uuid" AS "VideoImport.Video.uuid",
 146       "VideoImport->Video"."name" AS "VideoImport.Video.name",
 147       "Plugin"."id" AS "Plugin.id",
 148       "Plugin"."name" AS "Plugin.name",
 149       "Plugin"."type" AS "Plugin.type",
 150       "Plugin"."latestVersion" AS "Plugin.latestVersion",
 151       "Application"."id" AS "Application.id",
 152       "Application"."latestPeerTubeVersion" AS "Application.latestPeerTubeVersion",
 153       "ActorFollow"."id" AS "ActorFollow.id",
 154       "ActorFollow"."state" AS "ActorFollow.state",
 155       "ActorFollow->ActorFollower"."id" AS "ActorFollow.ActorFollower.id",
 156       "ActorFollow->ActorFollower"."preferredUsername" AS "ActorFollow.ActorFollower.preferredUsername",
 157       "ActorFollow->ActorFollower->Account"."id" AS "ActorFollow.ActorFollower.Account.id",
 158       "ActorFollow->ActorFollower->Account"."name" AS "ActorFollow.ActorFollower.Account.name",
 159       "ActorFollow->ActorFollower->Avatars"."id" AS "ActorFollow.ActorFollower.Avatars.id",
 160       "ActorFollow->ActorFollower->Avatars"."width" AS "ActorFollow.ActorFollower.Avatars.width",
 161       "ActorFollow->ActorFollower->Avatars"."type" AS "ActorFollow.ActorFollower.Avatars.type",
 162       "ActorFollow->ActorFollower->Avatars"."filename" AS "ActorFollow.ActorFollower.Avatars.filename",
 163       "ActorFollow->ActorFollower->Server"."id" AS "ActorFollow.ActorFollower.Server.id",
 164       "ActorFollow->ActorFollower->Server"."host" AS "ActorFollow.ActorFollower.Server.host",
 165       "ActorFollow->ActorFollowing"."id" AS "ActorFollow.ActorFollowing.id",
 166       "ActorFollow->ActorFollowing"."preferredUsername" AS "ActorFollow.ActorFollowing.preferredUsername",
 167       "ActorFollow->ActorFollowing"."type" AS "ActorFollow.ActorFollowing.type",
 168       "ActorFollow->ActorFollowing->VideoChannel"."id" AS "ActorFollow.ActorFollowing.VideoChannel.id",
 169       "ActorFollow->ActorFollowing->VideoChannel"."name" AS "ActorFollow.ActorFollowing.VideoChannel.name",
 170       "ActorFollow->ActorFollowing->Account"."id" AS "ActorFollow.ActorFollowing.Account.id",
 171       "ActorFollow->ActorFollowing->Account"."name" AS "ActorFollow.ActorFollowing.Account.name",
 172       "ActorFollow->ActorFollowing->Server"."id" AS "ActorFollow.ActorFollowing.Server.id",
 173       "ActorFollow->ActorFollowing->Server"."host" AS "ActorFollow.ActorFollowing.Server.host",
 174       "Account"."id" AS "Account.id",
 175       "Account"."name" AS "Account.name",
 176       "Account->Actor"."id" AS "Account.Actor.id",
 177       "Account->Actor"."preferredUsername" AS "Account.Actor.preferredUsername",
 178       "Account->Actor->Avatars"."id" AS "Account.Actor.Avatars.id",
 179       "Account->Actor->Avatars"."width" AS "Account.Actor.Avatars.width",
 180       "Account->Actor->Avatars"."type" AS "Account.Actor.Avatars.type",
 181       "Account->Actor->Avatars"."filename" AS "Account.Actor.Avatars.filename",
 182       "Account->Actor->Server"."id" AS "Account.Actor.Server.id",
 183       "Account->Actor->Server"."host" AS "Account.Actor.Server.host"`
 184   }
 185
 186   private getJoins () {
 187     return `
 188     LEFT JOIN (
 189       "video" AS "Video"
 190       INNER JOIN "videoChannel" AS "Video->VideoChannel" ON "Video"."channelId" = "Video->VideoChannel"."id"
 191       INNER JOIN "actor" AS "Video->VideoChannel->Actor" ON "Video->VideoChannel"."actorId" = "Video->VideoChannel->Actor"."id"
 192       LEFT JOIN "actorImage" AS "Video->VideoChannel->Actor->Avatars"
 193         ON "Video->VideoChannel->Actor"."id" = "Video->VideoChannel->Actor->Avatars"."actorId"
 194         AND "Video->VideoChannel->Actor->Avatars"."type" = ${ActorImageType.AVATAR}
 195       LEFT JOIN "server" AS "Video->VideoChannel->Actor->Server"
 196         ON "Video->VideoChannel->Actor"."serverId" = "Video->VideoChannel->Actor->Server"."id"
 197     ) ON "UserNotificationModel"."videoId" = "Video"."id"
 198
 199   LEFT JOIN (
 200     "videoComment" AS "VideoComment"
 201     INNER JOIN "account" AS "VideoComment->Account" ON "VideoComment"."accountId" = "VideoComment->Account"."id"
 202     INNER JOIN "actor" AS "VideoComment->Account->Actor" ON "VideoComment->Account"."actorId" = "VideoComment->Account->Actor"."id"
 203     LEFT JOIN "actorImage" AS "VideoComment->Account->Actor->Avatars"
 204       ON "VideoComment->Account->Actor"."id" = "VideoComment->Account->Actor->Avatars"."actorId"
 205       AND "VideoComment->Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}
 206     LEFT JOIN "server" AS "VideoComment->Account->Actor->Server"
 207       ON "VideoComment->Account->Actor"."serverId" = "VideoComment->Account->Actor->Server"."id"
 208     INNER JOIN "video" AS "VideoComment->Video" ON "VideoComment"."videoId" = "VideoComment->Video"."id"
 209   ) ON "UserNotificationModel"."commentId" = "VideoComment"."id"
 210
 211   LEFT JOIN "abuse" AS "Abuse" ON "UserNotificationModel"."abuseId" = "Abuse"."id"
 212   LEFT JOIN "videoAbuse" AS "Abuse->VideoAbuse" ON "Abuse"."id" = "Abuse->VideoAbuse"."abuseId"
 213   LEFT JOIN "video" AS "Abuse->VideoAbuse->Video" ON "Abuse->VideoAbuse"."videoId" = "Abuse->VideoAbuse->Video"."id"
 214   LEFT JOIN "commentAbuse" AS "Abuse->VideoCommentAbuse" ON "Abuse"."id" = "Abuse->VideoCommentAbuse"."abuseId"
 215   LEFT JOIN "videoComment" AS "Abuse->VideoCommentAbuse->VideoComment"
 216     ON "Abuse->VideoCommentAbuse"."videoCommentId" = "Abuse->VideoCommentAbuse->VideoComment"."id"
 217   LEFT JOIN "video" AS "Abuse->VideoCommentAbuse->VideoComment->Video"
 218     ON "Abuse->VideoCommentAbuse->VideoComment"."videoId" = "Abuse->VideoCommentAbuse->VideoComment->Video"."id"
 219   LEFT JOIN (
 220     "account" AS "Abuse->FlaggedAccount"
 221     INNER JOIN "actor" AS "Abuse->FlaggedAccount->Actor" ON "Abuse->FlaggedAccount"."actorId" = "Abuse->FlaggedAccount->Actor"."id"
 222     LEFT JOIN "actorImage" AS "Abuse->FlaggedAccount->Actor->Avatars"
 223       ON "Abuse->FlaggedAccount->Actor"."id" = "Abuse->FlaggedAccount->Actor->Avatars"."actorId"
 224       AND "Abuse->FlaggedAccount->Actor->Avatars"."type" = ${ActorImageType.AVATAR}
 225     LEFT JOIN "server" AS "Abuse->FlaggedAccount->Actor->Server"
 226       ON "Abuse->FlaggedAccount->Actor"."serverId" = "Abuse->FlaggedAccount->Actor->Server"."id"
 227   ) ON "Abuse"."flaggedAccountId" = "Abuse->FlaggedAccount"."id"
 228
 229   LEFT JOIN (
 230     "videoBlacklist" AS "VideoBlacklist"
 231     INNER JOIN "video" AS "VideoBlacklist->Video" ON "VideoBlacklist"."videoId" = "VideoBlacklist->Video"."id"
 232   ) ON "UserNotificationModel"."videoBlacklistId" = "VideoBlacklist"."id"
 233
 234   LEFT JOIN "videoImport" AS "VideoImport" ON "UserNotificationModel"."videoImportId" = "VideoImport"."id"
 235   LEFT JOIN "video" AS "VideoImport->Video" ON "VideoImport"."videoId" = "VideoImport->Video"."id"
 236
 237   LEFT JOIN "plugin" AS "Plugin" ON "UserNotificationModel"."pluginId" = "Plugin"."id"
 238
 239   LEFT JOIN "application" AS "Application" ON "UserNotificationModel"."applicationId" = "Application"."id"
 240
 241   LEFT JOIN (
 242     "actorFollow" AS "ActorFollow"
 243     INNER JOIN "actor" AS "ActorFollow->ActorFollower" ON "ActorFollow"."actorId" = "ActorFollow->ActorFollower"."id"
 244     INNER JOIN "account" AS "ActorFollow->ActorFollower->Account"
 245       ON "ActorFollow->ActorFollower"."id" = "ActorFollow->ActorFollower->Account"."actorId"
 246     LEFT JOIN "actorImage" AS "ActorFollow->ActorFollower->Avatars"
 247       ON "ActorFollow->ActorFollower"."id" = "ActorFollow->ActorFollower->Avatars"."actorId"
 248       AND "ActorFollow->ActorFollower->Avatars"."type" = ${ActorImageType.AVATAR}
 249     LEFT JOIN "server" AS "ActorFollow->ActorFollower->Server"
 250       ON "ActorFollow->ActorFollower"."serverId" = "ActorFollow->ActorFollower->Server"."id"
 251     INNER JOIN "actor" AS "ActorFollow->ActorFollowing" ON "ActorFollow"."targetActorId" = "ActorFollow->ActorFollowing"."id"
 252     LEFT JOIN "videoChannel" AS "ActorFollow->ActorFollowing->VideoChannel"
 253       ON "ActorFollow->ActorFollowing"."id" = "ActorFollow->ActorFollowing->VideoChannel"."actorId"
 254     LEFT JOIN "account" AS "ActorFollow->ActorFollowing->Account"
 255       ON "ActorFollow->ActorFollowing"."id" = "ActorFollow->ActorFollowing->Account"."actorId"
 256     LEFT JOIN "server" AS "ActorFollow->ActorFollowing->Server"
 257       ON "ActorFollow->ActorFollowing"."serverId" = "ActorFollow->ActorFollowing->Server"."id"
 258   ) ON "UserNotificationModel"."actorFollowId" = "ActorFollow"."id"
 259
 260   LEFT JOIN (
 261     "account" AS "Account"
 262     INNER JOIN "actor" AS "Account->Actor" ON "Account"."actorId" = "Account->Actor"."id"
 263     LEFT JOIN "actorImage" AS "Account->Actor->Avatars"
 264       ON "Account->Actor"."id" = "Account->Actor->Avatars"."actorId"
 265       AND "Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}
 266     LEFT JOIN "server" AS "Account->Actor->Server" ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"
 267   ) ON "UserNotificationModel"."accountId" = "Account"."id"`
 268   }
 269 }