aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/user/sql
diff options
context:
space:
mode:
Diffstat (limited to 'server/models/user/sql')
-rw-r--r--server/models/user/sql/user-notitication-list-query-builder.ts269
1 files changed, 269 insertions, 0 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
new file mode 100644
index 000000000..9eae4fc22
--- /dev/null
+++ b/server/models/user/sql/user-notitication-list-query-builder.ts
@@ -0,0 +1,269 @@
1import { QueryTypes, Sequelize } from 'sequelize'
2import { ModelBuilder } from '@server/models/shared'
3import { getSort } from '@server/models/utils'
4import { UserNotificationModelForApi } from '@server/types/models'
5import { ActorImageType } from '@shared/models'
6
7export interface ListNotificationsOptions {
8 userId: number
9 unread?: boolean
10 sort: string
11 offset: number
12 limit: number
13 sequelize: Sequelize
14}
15
16export class UserNotificationListQueryBuilder {
17 private innerQuery: string
18 private replacements: any = {}
19 private query: string
20
21 constructor (private readonly options: ListNotificationsOptions) {
22
23 }
24
25 async listNotifications () {
26 this.buildQuery()
27
28 const results = await this.options.sequelize.query(this.query, {
29 replacements: this.replacements,
30 type: QueryTypes.SELECT,
31 nest: true
32 })
33
34 const modelBuilder = new ModelBuilder<UserNotificationModelForApi>(this.options.sequelize)
35
36 return modelBuilder.createModels(results, 'UserNotification')
37 }
38
39 private buildInnerQuery () {
40 this.innerQuery = `SELECT * FROM "userNotification" AS "UserNotificationModel" ` +
41 `${this.getWhere()} ` +
42 `${this.getOrder()} ` +
43 `LIMIT :limit OFFSET :offset `
44
45 this.replacements.limit = this.options.limit
46 this.replacements.offset = this.options.offset
47 }
48
49 private buildQuery () {
50 this.buildInnerQuery()
51
52 this.query = `
53 ${this.getSelect()}
54 FROM (${this.innerQuery}) "UserNotificationModel"
55 ${this.getJoins()}
56 ${this.getOrder()}`
57 }
58
59 private getWhere () {
60 let base = '"UserNotificationModel"."userId" = :userId '
61 this.replacements.userId = this.options.userId
62
63 if (this.options.unread === true) {
64 base += 'AND "UserNotificationModel"."read" IS FALSE '
65 } else if (this.options.unread === false) {
66 base += 'AND "UserNotificationModel"."read" IS TRUE '
67 }
68
69 return `WHERE ${base}`
70 }
71
72 private getOrder () {
73 const orders = getSort(this.options.sort)
74
75 return 'ORDER BY ' + orders.map(o => `"UserNotificationModel"."${o[0]}" ${o[1]}`).join(', ')
76 }
77
78 private getSelect () {
79 return `SELECT
80 "UserNotificationModel"."id",
81 "UserNotificationModel"."type",
82 "UserNotificationModel"."read",
83 "UserNotificationModel"."createdAt",
84 "UserNotificationModel"."updatedAt",
85 "Video"."id" AS "Video.id",
86 "Video"."uuid" AS "Video.uuid",
87 "Video"."name" AS "Video.name",
88 "Video->VideoChannel"."id" AS "Video.VideoChannel.id",
89 "Video->VideoChannel"."name" AS "Video.VideoChannel.name",
90 "Video->VideoChannel->Actor"."id" AS "Video.VideoChannel.Actor.id",
91 "Video->VideoChannel->Actor"."preferredUsername" AS "Video.VideoChannel.Actor.preferredUsername",
92 "Video->VideoChannel->Actor->Avatars"."id" AS "Video.VideoChannel.Actor.Avatars.id",
93 "Video->VideoChannel->Actor->Avatars"."width" AS "Video.VideoChannel.Actor.Avatars.width",
94 "Video->VideoChannel->Actor->Avatars"."filename" AS "Video.VideoChannel.Actor.Avatars.filename",
95 "Video->VideoChannel->Actor->Server"."id" AS "Video.VideoChannel.Actor.Server.id",
96 "Video->VideoChannel->Actor->Server"."host" AS "Video.VideoChannel.Actor.Server.host",
97 "VideoComment"."id" AS "VideoComment.id",
98 "VideoComment"."originCommentId" AS "VideoComment.originCommentId",
99 "VideoComment->Account"."id" AS "VideoComment.Account.id",
100 "VideoComment->Account"."name" AS "VideoComment.Account.name",
101 "VideoComment->Account->Actor"."id" AS "VideoComment.Account.Actor.id",
102 "VideoComment->Account->Actor"."preferredUsername" AS "VideoComment.Account.Actor.preferredUsername",
103 "VideoComment->Account->Actor->Avatars"."id" AS "VideoComment.Account.Actor.Avatars.id",
104 "VideoComment->Account->Actor->Avatars"."width" AS "VideoComment.Account.Actor.Avatars.width",
105 "VideoComment->Account->Actor->Avatars"."filename" AS "VideoComment.Account.Actor.Avatars.filename",
106 "VideoComment->Account->Actor->Server"."id" AS "VideoComment.Account.Actor.Server.id",
107 "VideoComment->Account->Actor->Server"."host" AS "VideoComment.Account.Actor.Server.host",
108 "VideoComment->Video"."id" AS "VideoComment.Video.id",
109 "VideoComment->Video"."uuid" AS "VideoComment.Video.uuid",
110 "VideoComment->Video"."name" AS "VideoComment.Video.name",
111 "Abuse"."id" AS "Abuse.id",
112 "Abuse"."state" AS "Abuse.state",
113 "Abuse->VideoAbuse"."id" AS "Abuse.VideoAbuse.id",
114 "Abuse->VideoAbuse->Video"."id" AS "Abuse.VideoAbuse.Video.id",
115 "Abuse->VideoAbuse->Video"."uuid" AS "Abuse.VideoAbuse.Video.uuid",
116 "Abuse->VideoAbuse->Video"."name" AS "Abuse.VideoAbuse.Video.name",
117 "Abuse->VideoCommentAbuse"."id" AS "Abuse.VideoCommentAbuse.id",
118 "Abuse->VideoCommentAbuse->VideoComment"."id" AS "Abuse.VideoCommentAbuse.VideoComment.id",
119 "Abuse->VideoCommentAbuse->VideoComment"."originCommentId" AS "Abuse.VideoCommentAbuse.VideoComment.originCommentId",
120 "Abuse->VideoCommentAbuse->VideoComment->Video"."id" AS "Abuse.VideoCommentAbuse.VideoComment.Video.id",
121 "Abuse->VideoCommentAbuse->VideoComment->Video"."name" AS "Abuse.VideoCommentAbuse.VideoComment.Video.name",
122 "Abuse->VideoCommentAbuse->VideoComment->Video"."uuid" AS "Abuse.VideoCommentAbuse.VideoComment.Video.uuid",
123 "Abuse->FlaggedAccount"."id" AS "Abuse.FlaggedAccount.id",
124 "Abuse->FlaggedAccount"."name" AS "Abuse.FlaggedAccount.name",
125 "Abuse->FlaggedAccount"."description" AS "Abuse.FlaggedAccount.description",
126 "Abuse->FlaggedAccount"."actorId" AS "Abuse.FlaggedAccount.actorId",
127 "Abuse->FlaggedAccount"."userId" AS "Abuse.FlaggedAccount.userId",
128 "Abuse->FlaggedAccount"."applicationId" AS "Abuse.FlaggedAccount.applicationId",
129 "Abuse->FlaggedAccount"."createdAt" AS "Abuse.FlaggedAccount.createdAt",
130 "Abuse->FlaggedAccount"."updatedAt" AS "Abuse.FlaggedAccount.updatedAt",
131 "Abuse->FlaggedAccount->Actor"."id" AS "Abuse.FlaggedAccount.Actor.id",
132 "Abuse->FlaggedAccount->Actor"."preferredUsername" AS "Abuse.FlaggedAccount.Actor.preferredUsername",
133 "Abuse->FlaggedAccount->Actor->Avatars"."id" AS "Abuse.FlaggedAccount.Actor.Avatars.id",
134 "Abuse->FlaggedAccount->Actor->Avatars"."width" AS "Abuse.FlaggedAccount.Actor.Avatars.width",
135 "Abuse->FlaggedAccount->Actor->Avatars"."filename" AS "Abuse.FlaggedAccount.Actor.Avatars.filename",
136 "Abuse->FlaggedAccount->Actor->Server"."id" AS "Abuse.FlaggedAccount.Actor.Server.id",
137 "Abuse->FlaggedAccount->Actor->Server"."host" AS "Abuse.FlaggedAccount.Actor.Server.host",
138 "VideoBlacklist"."id" AS "VideoBlacklist.id",
139 "VideoBlacklist->Video"."id" AS "VideoBlacklist.Video.id",
140 "VideoBlacklist->Video"."uuid" AS "VideoBlacklist.Video.uuid",
141 "VideoBlacklist->Video"."name" AS "VideoBlacklist.Video.name",
142 "VideoImport"."id" AS "VideoImport.id",
143 "VideoImport"."magnetUri" AS "VideoImport.magnetUri",
144 "VideoImport"."targetUrl" AS "VideoImport.targetUrl",
145 "VideoImport"."torrentName" AS "VideoImport.torrentName",
146 "VideoImport->Video"."id" AS "VideoImport.Video.id",
147 "VideoImport->Video"."uuid" AS "VideoImport.Video.uuid",
148 "VideoImport->Video"."name" AS "VideoImport.Video.name",
149 "Plugin"."id" AS "Plugin.id",
150 "Plugin"."name" AS "Plugin.name",
151 "Plugin"."type" AS "Plugin.type",
152 "Plugin"."latestVersion" AS "Plugin.latestVersion",
153 "Application"."id" AS "Application.id",
154 "Application"."latestPeerTubeVersion" AS "Application.latestPeerTubeVersion",
155 "ActorFollow"."id" AS "ActorFollow.id",
156 "ActorFollow"."state" AS "ActorFollow.state",
157 "ActorFollow->ActorFollower"."id" AS "ActorFollow.ActorFollower.id",
158 "ActorFollow->ActorFollower"."preferredUsername" AS "ActorFollow.ActorFollower.preferredUsername",
159 "ActorFollow->ActorFollower->Account"."id" AS "ActorFollow.ActorFollower.Account.id",
160 "ActorFollow->ActorFollower->Account"."name" AS "ActorFollow.ActorFollower.Account.name",
161 "ActorFollow->ActorFollower->Avatars"."id" AS "ActorFollow.ActorFollower.Avatars.id",
162 "ActorFollow->ActorFollower->Avatars"."width" AS "ActorFollow.ActorFollower.Avatars.width",
163 "ActorFollow->ActorFollower->Avatars"."filename" AS "ActorFollow.ActorFollower.Avatars.filename",
164 "ActorFollow->ActorFollower->Server"."id" AS "ActorFollow.ActorFollower.Server.id",
165 "ActorFollow->ActorFollower->Server"."host" AS "ActorFollow.ActorFollower.Server.host",
166 "ActorFollow->ActorFollowing"."id" AS "ActorFollow.ActorFollowing.id",
167 "ActorFollow->ActorFollowing"."preferredUsername" AS "ActorFollow.ActorFollowing.preferredUsername",
168 "ActorFollow->ActorFollowing"."type" AS "ActorFollow.ActorFollowing.type",
169 "ActorFollow->ActorFollowing->VideoChannel"."id" AS "ActorFollow.ActorFollowing.VideoChannel.id",
170 "ActorFollow->ActorFollowing->VideoChannel"."name" AS "ActorFollow.ActorFollowing.VideoChannel.name",
171 "ActorFollow->ActorFollowing->Account"."id" AS "ActorFollow.ActorFollowing.Account.id",
172 "ActorFollow->ActorFollowing->Account"."name" AS "ActorFollow.ActorFollowing.Account.name",
173 "ActorFollow->ActorFollowing->Server"."id" AS "ActorFollow.ActorFollowing.Server.id",
174 "ActorFollow->ActorFollowing->Server"."host" AS "ActorFollow.ActorFollowing.Server.host",
175 "Account"."id" AS "Account.id",
176 "Account"."name" AS "Account.name",
177 "Account->Actor"."id" AS "Account.Actor.id",
178 "Account->Actor"."preferredUsername" AS "Account.Actor.preferredUsername",
179 "Account->Actor->Avatars"."id" AS "Account.Actor.Avatars.id",
180 "Account->Actor->Avatars"."width" AS "Account.Actor.Avatars.width",
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}
generated by cgit v1.2.3 (git 2.25.1) at 2024-12-24 14:03:04 +0000