diff options
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.ts | 269 |
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 @@ | |||
1 | import { QueryTypes, Sequelize } from 'sequelize' | ||
2 | import { ModelBuilder } from '@server/models/shared' | ||
3 | import { getSort } from '@server/models/utils' | ||
4 | import { UserNotificationModelForApi } from '@server/types/models' | ||
5 | import { ActorImageType } from '@shared/models' | ||
6 | |||
7 | export interface ListNotificationsOptions { | ||
8 | userId: number | ||
9 | unread?: boolean | ||
10 | sort: string | ||
11 | offset: number | ||
12 | limit: number | ||
13 | sequelize: Sequelize | ||
14 | } | ||
15 | |||
16 | export 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 | } | ||