1 import { Model, Sequelize, Transaction } from 'sequelize'
2 import { AbstractRunQuery, ModelBuilder } from '@server/models/shared'
3 import { ActorImageType, VideoPrivacy } from '@shared/models'
4 import { createSafeIn, getSort, parseRowCountResult } from '../../../shared'
5 import { VideoCommentTableAttributes } from './video-comment-table-attributes'
7 export interface ListVideoCommentsOptions {
8 selectType: 'api' | 'feed' | 'comment-only'
17 videoChannelId?: number
19 blockerAccountIds?: number[]
24 onLocalVideo?: boolean
25 onPublicVideo?: boolean
26 videoAccountOwnerId?: boolean
29 searchAccount?: string
32 includeReplyCounters?: boolean
34 transaction?: Transaction
37 export class VideoCommentListQueryBuilder extends AbstractRunQuery {
38 private readonly tableAttributes = new VideoCommentTableAttributes()
40 private innerQuery: string
45 private innerSelect = ''
46 private innerJoins = ''
47 private innerLateralJoins = ''
48 private innerWhere = ''
50 private readonly built = {
54 videoChannelJoin: false,
59 protected readonly sequelize: Sequelize,
60 private readonly options: ListVideoCommentsOptions
64 if (this.options.includeReplyCounters && !this.options.videoId) {
65 throw new Error('Cannot include reply counters without videoId')
69 async listComments <T extends Model> () {
72 const results = await this.runQuery({ nest: true, transaction: this.options.transaction })
73 const modelBuilder = new ModelBuilder<T>(this.sequelize)
75 return modelBuilder.createModels(results, 'VideoComment')
78 async countComments () {
79 this.buildCountQuery()
81 const result = await this.runQuery({ transaction: this.options.transaction })
83 return parseRowCountResult(result)
86 // ---------------------------------------------------------------------------
88 private buildListQuery () {
89 this.buildInnerListQuery()
90 this.buildListSelect()
92 this.query = `${this.select} ` +
93 `FROM (${this.innerQuery}) AS "VideoCommentModel" ` +
98 private buildInnerListQuery () {
100 this.buildInnerListSelect()
102 this.innerQuery = `${this.innerSelect} ` +
103 `FROM "videoComment" AS "VideoCommentModel" ` +
104 `${this.innerJoins} ` +
105 `${this.innerLateralJoins} ` +
106 `${this.innerWhere} ` +
107 `${this.getOrder()} ` +
108 `${this.getInnerLimit()}`
111 // ---------------------------------------------------------------------------
113 private buildCountQuery () {
116 this.query = `SELECT COUNT(*) AS "total" ` +
117 `FROM "videoComment" AS "VideoCommentModel" ` +
118 `${this.innerJoins} ` +
122 // ---------------------------------------------------------------------------
124 private buildWhere () {
125 let where: string[] = []
127 if (this.options.videoId) {
128 this.replacements.videoId = this.options.videoId
130 where.push('"VideoCommentModel"."videoId" = :videoId')
133 if (this.options.threadId) {
134 this.replacements.threadId = this.options.threadId
136 where.push('("VideoCommentModel"."id" = :threadId OR "VideoCommentModel"."originCommentId" = :threadId)')
139 if (this.options.accountId) {
140 this.replacements.accountId = this.options.accountId
142 where.push('"VideoCommentModel"."accountId" = :accountId')
145 if (this.options.videoChannelId) {
146 this.buildVideoChannelJoin()
148 this.replacements.videoChannelId = this.options.videoChannelId
150 where.push('"Account->VideoChannel"."id" = :videoChannelId')
153 if (this.options.blockerAccountIds) {
154 this.buildVideoChannelJoin()
156 where = where.concat(this.getBlockWhere('VideoCommentModel', 'Video->VideoChannel'))
159 if (this.options.isThread === true) {
160 where.push('"VideoCommentModel"."inReplyToCommentId" IS NULL')
163 if (this.options.notDeleted === true) {
164 where.push('"VideoCommentModel"."deletedAt" IS NULL')
167 if (this.options.isLocal === true) {
168 this.buildAccountJoin()
170 where.push('"Account->Actor"."serverId" IS NULL')
171 } else if (this.options.isLocal === false) {
172 this.buildAccountJoin()
174 where.push('"Account->Actor"."serverId" IS NOT NULL')
177 if (this.options.onLocalVideo === true) {
178 this.buildVideoJoin()
180 where.push('"Video"."remote" IS FALSE')
181 } else if (this.options.onLocalVideo === false) {
182 this.buildVideoJoin()
184 where.push('"Video"."remote" IS TRUE')
187 if (this.options.onPublicVideo === true) {
188 this.buildVideoJoin()
190 where.push(`"Video"."privacy" = ${VideoPrivacy.PUBLIC}`)
193 if (this.options.videoAccountOwnerId) {
194 this.buildVideoChannelJoin()
196 this.replacements.videoAccountOwnerId = this.options.videoAccountOwnerId
198 where.push(`"Video->VideoChannel"."accountId" = :videoAccountOwnerId`)
201 if (this.options.search) {
202 this.buildVideoJoin()
203 this.buildAccountJoin()
205 const escapedLikeSearch = this.sequelize.escape('%' + this.options.search + '%')
209 `"VideoCommentModel"."text" ILIKE ${escapedLikeSearch} OR ` +
210 `"Account->Actor"."preferredUsername" ILIKE ${escapedLikeSearch} OR ` +
211 `"Account"."name" ILIKE ${escapedLikeSearch} OR ` +
212 `"Video"."name" ILIKE ${escapedLikeSearch} ` +
217 if (this.options.searchAccount) {
218 this.buildAccountJoin()
220 const escapedLikeSearch = this.sequelize.escape('%' + this.options.searchAccount + '%')
224 `"Account->Actor"."preferredUsername" ILIKE ${escapedLikeSearch} OR ` +
225 `"Account"."name" ILIKE ${escapedLikeSearch} ` +
230 if (this.options.searchVideo) {
231 this.buildVideoJoin()
233 const escapedLikeSearch = this.sequelize.escape('%' + this.options.searchVideo + '%')
235 where.push(`"Video"."name" ILIKE ${escapedLikeSearch}`)
238 if (where.length !== 0) {
239 this.innerWhere = `WHERE ${where.join(' AND ')}`
243 private buildAccountJoin () {
244 if (this.built.accountJoin) return
246 this.innerJoins += ' LEFT JOIN "account" "Account" ON "Account"."id" = "VideoCommentModel"."accountId" ' +
247 'LEFT JOIN "actor" "Account->Actor" ON "Account->Actor"."id" = "Account"."actorId" ' +
248 'LEFT JOIN "server" "Account->Actor->Server" ON "Account->Actor"."serverId" = "Account->Actor->Server"."id" '
250 this.built.accountJoin = true
253 private buildVideoJoin () {
254 if (this.built.videoJoin) return
256 this.innerJoins += ' LEFT JOIN "video" "Video" ON "Video"."id" = "VideoCommentModel"."videoId" '
258 this.built.videoJoin = true
261 private buildVideoChannelJoin () {
262 if (this.built.videoChannelJoin) return
264 this.buildVideoJoin()
266 this.innerJoins += ' LEFT JOIN "videoChannel" "Video->VideoChannel" ON "Video"."channelId" = "Video->VideoChannel"."id" '
268 this.built.videoChannelJoin = true
271 private buildAvatarsJoin () {
272 if (this.options.selectType !== 'api' && this.options.selectType !== 'feed') return ''
273 if (this.built.avatarJoin) return
275 this.joins += `LEFT JOIN "actorImage" "Account->Actor->Avatars" ` +
276 `ON "VideoCommentModel"."Account.Actor.id" = "Account->Actor->Avatars"."actorId" ` +
277 `AND "Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}`
279 this.built.avatarJoin = true
282 // ---------------------------------------------------------------------------
284 private buildListSelect () {
285 const toSelect = [ '"VideoCommentModel".*' ]
287 if (this.options.selectType === 'api' || this.options.selectType === 'feed') {
288 this.buildAvatarsJoin()
290 toSelect.push(this.tableAttributes.getAvatarAttributes())
293 this.select = this.buildSelect(toSelect)
296 private buildInnerListSelect () {
297 let toSelect = [ this.tableAttributes.getVideoCommentAttributes() ]
299 if (this.options.selectType === 'api' || this.options.selectType === 'feed') {
300 this.buildAccountJoin()
301 this.buildVideoJoin()
303 toSelect = toSelect.concat([
304 this.tableAttributes.getVideoAttributes(),
305 this.tableAttributes.getAccountAttributes(),
306 this.tableAttributes.getActorAttributes(),
307 this.tableAttributes.getServerAttributes()
311 if (this.options.includeReplyCounters === true) {
312 this.buildTotalRepliesSelect()
313 this.buildAuthorTotalRepliesSelect()
315 toSelect.push('"totalRepliesFromVideoAuthor"."count" AS "totalRepliesFromVideoAuthor"')
316 toSelect.push('"totalReplies"."count" AS "totalReplies"')
319 this.innerSelect = this.buildSelect(toSelect)
322 // ---------------------------------------------------------------------------
324 private getBlockWhere (commentTableName: string, channelTableName: string) {
325 const where: string[] = []
327 const blockerIdsString = createSafeIn(
329 this.options.blockerAccountIds,
330 [ `"${channelTableName}"."accountId"` ]
335 `SELECT 1 FROM "accountBlocklist" ` +
336 `WHERE "targetAccountId" = "${commentTableName}"."accountId" ` +
337 `AND "accountId" IN (${blockerIdsString})` +
343 `SELECT 1 FROM "account" ` +
344 `INNER JOIN "actor" ON account."actorId" = actor.id ` +
345 `INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ` +
346 `WHERE "account"."id" = "${commentTableName}"."accountId" ` +
347 `AND "serverBlocklist"."accountId" IN (${blockerIdsString})` +
354 // ---------------------------------------------------------------------------
356 private buildTotalRepliesSelect () {
357 const blockWhereString = this.getBlockWhere('replies', 'videoChannel').join(' AND ')
359 // Help the planner by providing videoId that should filter out many comments
360 this.replacements.videoId = this.options.videoId
362 this.innerLateralJoins += `LEFT JOIN LATERAL (` +
363 `SELECT COUNT("replies"."id") AS "count" FROM "videoComment" AS "replies" ` +
364 `INNER JOIN "video" ON "video"."id" = "replies"."videoId" AND "replies"."videoId" = :videoId ` +
365 `LEFT JOIN "videoChannel" ON "video"."channelId" = "videoChannel"."id" ` +
366 `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ` +
367 `AND "deletedAt" IS NULL ` +
368 `AND ${blockWhereString} ` +
369 `) "totalReplies" ON TRUE `
372 private buildAuthorTotalRepliesSelect () {
373 // Help the planner by providing videoId that should filter out many comments
374 this.replacements.videoId = this.options.videoId
376 this.innerLateralJoins += `LEFT JOIN LATERAL (` +
377 `SELECT COUNT("replies"."id") AS "count" FROM "videoComment" AS "replies" ` +
378 `INNER JOIN "video" ON "video"."id" = "replies"."videoId" AND "replies"."videoId" = :videoId ` +
379 `INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ` +
380 `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" AND "replies"."accountId" = "videoChannel"."accountId"` +
381 `) "totalRepliesFromVideoAuthor" ON TRUE `
384 private getOrder () {
385 if (!this.options.sort) return ''
387 const orders = getSort(this.options.sort)
389 return 'ORDER BY ' + orders.map(o => `"${o[0]}" ${o[1]}`).join(', ')
392 private getInnerLimit () {
393 if (!this.options.count) return ''
395 this.replacements.limit = this.options.count
396 this.replacements.offset = this.options.start || 0
398 return `LIMIT :limit OFFSET :offset `