aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/user/sql/user-notitication-list-query-builder.ts
diff options
context:
space:
mode:
Diffstat (limited to 'server/models/user/sql/user-notitication-list-query-builder.ts')
-rw-r--r--server/models/user/sql/user-notitication-list-query-builder.ts273
1 files changed, 0 insertions, 273 deletions
diff --git a/server/models/user/sql/user-notitication-list-query-builder.ts b/server/models/user/sql/user-notitication-list-query-builder.ts
deleted file mode 100644
index 7b29807a3..000000000
--- a/server/models/user/sql/user-notitication-list-query-builder.ts
+++ /dev/null
@@ -1,273 +0,0 @@
1import { Sequelize } from 'sequelize'
2import { AbstractRunQuery, ModelBuilder } from '@server/models/shared'
3import { UserNotificationModelForApi } from '@server/types/models'
4import { ActorImageType } from '@shared/models'
5import { getSort } from '../../shared'
6
7export interface ListNotificationsOptions {
8 userId: number
9 unread?: boolean
10 sort: string
11 offset: number
12 limit: number
13}
14
15export 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 "UserRegistration"."id" AS "UserRegistration.id",
185 "UserRegistration"."username" AS "UserRegistration.username"`
186 }
187
188 private getJoins () {
189 return `
190 LEFT JOIN (
191 "video" AS "Video"
192 INNER JOIN "videoChannel" AS "Video->VideoChannel" ON "Video"."channelId" = "Video->VideoChannel"."id"
193 INNER JOIN "actor" AS "Video->VideoChannel->Actor" ON "Video->VideoChannel"."actorId" = "Video->VideoChannel->Actor"."id"
194 LEFT JOIN "actorImage" AS "Video->VideoChannel->Actor->Avatars"
195 ON "Video->VideoChannel->Actor"."id" = "Video->VideoChannel->Actor->Avatars"."actorId"
196 AND "Video->VideoChannel->Actor->Avatars"."type" = ${ActorImageType.AVATAR}
197 LEFT JOIN "server" AS "Video->VideoChannel->Actor->Server"
198 ON "Video->VideoChannel->Actor"."serverId" = "Video->VideoChannel->Actor->Server"."id"
199 ) ON "UserNotificationModel"."videoId" = "Video"."id"
200
201 LEFT JOIN (
202 "videoComment" AS "VideoComment"
203 INNER JOIN "account" AS "VideoComment->Account" ON "VideoComment"."accountId" = "VideoComment->Account"."id"
204 INNER JOIN "actor" AS "VideoComment->Account->Actor" ON "VideoComment->Account"."actorId" = "VideoComment->Account->Actor"."id"
205 LEFT JOIN "actorImage" AS "VideoComment->Account->Actor->Avatars"
206 ON "VideoComment->Account->Actor"."id" = "VideoComment->Account->Actor->Avatars"."actorId"
207 AND "VideoComment->Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}
208 LEFT JOIN "server" AS "VideoComment->Account->Actor->Server"
209 ON "VideoComment->Account->Actor"."serverId" = "VideoComment->Account->Actor->Server"."id"
210 INNER JOIN "video" AS "VideoComment->Video" ON "VideoComment"."videoId" = "VideoComment->Video"."id"
211 ) ON "UserNotificationModel"."commentId" = "VideoComment"."id"
212
213 LEFT JOIN "abuse" AS "Abuse" ON "UserNotificationModel"."abuseId" = "Abuse"."id"
214 LEFT JOIN "videoAbuse" AS "Abuse->VideoAbuse" ON "Abuse"."id" = "Abuse->VideoAbuse"."abuseId"
215 LEFT JOIN "video" AS "Abuse->VideoAbuse->Video" ON "Abuse->VideoAbuse"."videoId" = "Abuse->VideoAbuse->Video"."id"
216 LEFT JOIN "commentAbuse" AS "Abuse->VideoCommentAbuse" ON "Abuse"."id" = "Abuse->VideoCommentAbuse"."abuseId"
217 LEFT JOIN "videoComment" AS "Abuse->VideoCommentAbuse->VideoComment"
218 ON "Abuse->VideoCommentAbuse"."videoCommentId" = "Abuse->VideoCommentAbuse->VideoComment"."id"
219 LEFT JOIN "video" AS "Abuse->VideoCommentAbuse->VideoComment->Video"
220 ON "Abuse->VideoCommentAbuse->VideoComment"."videoId" = "Abuse->VideoCommentAbuse->VideoComment->Video"."id"
221 LEFT JOIN (
222 "account" AS "Abuse->FlaggedAccount"
223 INNER JOIN "actor" AS "Abuse->FlaggedAccount->Actor" ON "Abuse->FlaggedAccount"."actorId" = "Abuse->FlaggedAccount->Actor"."id"
224 LEFT JOIN "actorImage" AS "Abuse->FlaggedAccount->Actor->Avatars"
225 ON "Abuse->FlaggedAccount->Actor"."id" = "Abuse->FlaggedAccount->Actor->Avatars"."actorId"
226 AND "Abuse->FlaggedAccount->Actor->Avatars"."type" = ${ActorImageType.AVATAR}
227 LEFT JOIN "server" AS "Abuse->FlaggedAccount->Actor->Server"
228 ON "Abuse->FlaggedAccount->Actor"."serverId" = "Abuse->FlaggedAccount->Actor->Server"."id"
229 ) ON "Abuse"."flaggedAccountId" = "Abuse->FlaggedAccount"."id"
230
231 LEFT JOIN (
232 "videoBlacklist" AS "VideoBlacklist"
233 INNER JOIN "video" AS "VideoBlacklist->Video" ON "VideoBlacklist"."videoId" = "VideoBlacklist->Video"."id"
234 ) ON "UserNotificationModel"."videoBlacklistId" = "VideoBlacklist"."id"
235
236 LEFT JOIN "videoImport" AS "VideoImport" ON "UserNotificationModel"."videoImportId" = "VideoImport"."id"
237 LEFT JOIN "video" AS "VideoImport->Video" ON "VideoImport"."videoId" = "VideoImport->Video"."id"
238
239 LEFT JOIN "plugin" AS "Plugin" ON "UserNotificationModel"."pluginId" = "Plugin"."id"
240
241 LEFT JOIN "application" AS "Application" ON "UserNotificationModel"."applicationId" = "Application"."id"
242
243 LEFT JOIN (
244 "actorFollow" AS "ActorFollow"
245 INNER JOIN "actor" AS "ActorFollow->ActorFollower" ON "ActorFollow"."actorId" = "ActorFollow->ActorFollower"."id"
246 INNER JOIN "account" AS "ActorFollow->ActorFollower->Account"
247 ON "ActorFollow->ActorFollower"."id" = "ActorFollow->ActorFollower->Account"."actorId"
248 LEFT JOIN "actorImage" AS "ActorFollow->ActorFollower->Avatars"
249 ON "ActorFollow->ActorFollower"."id" = "ActorFollow->ActorFollower->Avatars"."actorId"
250 AND "ActorFollow->ActorFollower->Avatars"."type" = ${ActorImageType.AVATAR}
251 LEFT JOIN "server" AS "ActorFollow->ActorFollower->Server"
252 ON "ActorFollow->ActorFollower"."serverId" = "ActorFollow->ActorFollower->Server"."id"
253 INNER JOIN "actor" AS "ActorFollow->ActorFollowing" ON "ActorFollow"."targetActorId" = "ActorFollow->ActorFollowing"."id"
254 LEFT JOIN "videoChannel" AS "ActorFollow->ActorFollowing->VideoChannel"
255 ON "ActorFollow->ActorFollowing"."id" = "ActorFollow->ActorFollowing->VideoChannel"."actorId"
256 LEFT JOIN "account" AS "ActorFollow->ActorFollowing->Account"
257 ON "ActorFollow->ActorFollowing"."id" = "ActorFollow->ActorFollowing->Account"."actorId"
258 LEFT JOIN "server" AS "ActorFollow->ActorFollowing->Server"
259 ON "ActorFollow->ActorFollowing"."serverId" = "ActorFollow->ActorFollowing->Server"."id"
260 ) ON "UserNotificationModel"."actorFollowId" = "ActorFollow"."id"
261
262 LEFT JOIN (
263 "account" AS "Account"
264 INNER JOIN "actor" AS "Account->Actor" ON "Account"."actorId" = "Account->Actor"."id"
265 LEFT JOIN "actorImage" AS "Account->Actor->Avatars"
266 ON "Account->Actor"."id" = "Account->Actor->Avatars"."actorId"
267 AND "Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}
268 LEFT JOIN "server" AS "Account->Actor->Server" ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"
269 ) ON "UserNotificationModel"."accountId" = "Account"."id"
270
271 LEFT JOIN "userRegistration" as "UserRegistration" ON "UserNotificationModel"."userRegistrationId" = "UserRegistration"."id"`
272 }
273}
generated by cgit v1.2.3 (git 2.25.1) at 2025-04-15 07:24:01 +0000