]>
Commit | Line | Data |
---|---|---|
156c44c8 C |
1 | import { Sequelize } from 'sequelize' |
2 | import { AbstractRunQuery, ModelBuilder } from '@server/models/shared' | |
d0800f76 | 3 | import { UserNotificationModelForApi } from '@server/types/models' |
4 | import { ActorImageType } from '@shared/models' | |
8c4bbd94 | 5 | import { getSort } from '../../shared' |
d0800f76 | 6 | |
7 | export interface ListNotificationsOptions { | |
8 | userId: number | |
9 | unread?: boolean | |
10 | sort: string | |
11 | offset: number | |
12 | limit: number | |
d0800f76 | 13 | } |
14 | ||
156c44c8 | 15 | export class UserNotificationListQueryBuilder extends AbstractRunQuery { |
d0800f76 | 16 | private innerQuery: string |
d0800f76 | 17 | |
156c44c8 C |
18 | constructor ( |
19 | protected readonly sequelize: Sequelize, | |
20 | private readonly options: ListNotificationsOptions | |
21 | ) { | |
22 | super(sequelize) | |
d0800f76 | 23 | } |
24 | ||
25 | async listNotifications () { | |
26 | this.buildQuery() | |
27 | ||
156c44c8 C |
28 | const results = await this.runQuery({ nest: true }) |
29 | const modelBuilder = new ModelBuilder<UserNotificationModelForApi>(this.sequelize) | |
d0800f76 | 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", | |
a220b84b | 89 | "Video->VideoChannel->Actor->Avatars"."type" AS "Video.VideoChannel.Actor.Avatars.type", |
d0800f76 | 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", | |
a220b84b | 101 | "VideoComment->Account->Actor->Avatars"."type" AS "VideoComment.Account.Actor.Avatars.type", |
d0800f76 | 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", | |
a220b84b | 132 | "Abuse->FlaggedAccount->Actor->Avatars"."type" AS "Abuse.FlaggedAccount.Actor.Avatars.type", |
d0800f76 | 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", | |
a220b84b | 161 | "ActorFollow->ActorFollower->Avatars"."type" AS "ActorFollow.ActorFollower.Avatars.type", |
d0800f76 | 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", | |
a220b84b | 180 | "Account->Actor->Avatars"."type" AS "Account.Actor.Avatars.type", |
d0800f76 | 181 | "Account->Actor->Avatars"."filename" AS "Account.Actor.Avatars.filename", |
182 | "Account->Actor->Server"."id" AS "Account.Actor.Server.id", | |
e364e31e C |
183 | "Account->Actor->Server"."host" AS "Account.Actor.Server.host", |
184 | "UserRegistration"."id" AS "UserRegistration.id", | |
185 | "UserRegistration"."username" AS "UserRegistration.username"` | |
d0800f76 | 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 | ||
e364e31e C |
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" | |
d0800f76 | 230 | |
e364e31e C |
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" | |
d0800f76 | 235 | |
e364e31e C |
236 | LEFT JOIN "videoImport" AS "VideoImport" ON "UserNotificationModel"."videoImportId" = "VideoImport"."id" |
237 | LEFT JOIN "video" AS "VideoImport->Video" ON "VideoImport"."videoId" = "VideoImport->Video"."id" | |
d0800f76 | 238 | |
e364e31e | 239 | LEFT JOIN "plugin" AS "Plugin" ON "UserNotificationModel"."pluginId" = "Plugin"."id" |
d0800f76 | 240 | |
e364e31e | 241 | LEFT JOIN "application" AS "Application" ON "UserNotificationModel"."applicationId" = "Application"."id" |
d0800f76 | 242 | |
e364e31e C |
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" | |
d0800f76 | 261 | |
e364e31e C |
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" | |
d0800f76 | 270 | |
e364e31e | 271 | LEFT JOIN "userRegistration" as "UserRegistration" ON "UserNotificationModel"."userRegistrationId" = "UserRegistration"."id"` |
d0800f76 | 272 | } |
273 | } |