server/models/video/sql/comment/video-comment-list-query-builder.ts

   1 import { Model, Sequelize, Transaction } from 'sequelize'
   2 import { AbstractRunQuery, ModelBuilder } from '@server/models/shared'
   3 import { createSafeIn, getCommentSort, parseRowCountResult } from '@server/models/utils'
   4 import { ActorImageType, VideoPrivacy } from '@shared/models'
   5 import { VideoCommentTableAttributes } from './video-comment-table-attributes'
   6
   7 export interface ListVideoCommentsOptions {
   8   selectType: 'api' | 'feed' | 'comment-only'
   9
  10   start?: number
  11   count?: number
  12   sort?: string
  13
  14   videoId?: number
  15   threadId?: number
  16   accountId?: number
  17   videoChannelId?: number
  18
  19   blockerAccountIds?: number[]
  20
  21   isThread?: boolean
  22   notDeleted?: boolean
  23   isLocal?: boolean
  24   onLocalVideo?: boolean
  25   onPublicVideo?: boolean
  26   videoAccountOwnerId?: boolean
  27
  28   search?: string
  29   searchAccount?: string
  30   searchVideo?: string
  31
  32   includeReplyCounters?: boolean
  33
  34   transaction?: Transaction
  35 }
  36
  37 export class VideoCommentListQueryBuilder extends AbstractRunQuery {
  38   private readonly tableAttributes = new VideoCommentTableAttributes()
  39
  40   private innerQuery: string
  41
  42   private select = ''
  43   private joins = ''
  44
  45   private innerSelect = ''
  46   private innerJoins = ''
  47   private innerWhere = ''
  48
  49   private readonly built = {
  50     cte: false,
  51     accountJoin: false,
  52     videoJoin: false,
  53     videoChannelJoin: false,
  54     avatarJoin: false
  55   }
  56
  57   constructor (
  58     protected readonly sequelize: Sequelize,
  59     private readonly options: ListVideoCommentsOptions
  60   ) {
  61     super(sequelize)
  62   }
  63
  64   async listComments <T extends Model> () {
  65     this.buildListQuery()
  66
  67     const results = await this.runQuery({ nest: true, transaction: this.options.transaction })
  68     const modelBuilder = new ModelBuilder<T>(this.sequelize)
  69
  70     return modelBuilder.createModels(results, 'VideoComment')
  71   }
  72
  73   async countComments () {
  74     this.buildCountQuery()
  75
  76     const result = await this.runQuery({ transaction: this.options.transaction })
  77
  78     return parseRowCountResult(result)
  79   }
  80
  81   // ---------------------------------------------------------------------------
  82
  83   private buildListQuery () {
  84     this.buildInnerListQuery()
  85     this.buildListSelect()
  86
  87     this.query = `${this.select} ` +
  88       `FROM (${this.innerQuery}) AS "VideoCommentModel" ` +
  89       `${this.joins} ` +
  90       `${this.getOrder()} ` +
  91       `${this.getLimit()}`
  92   }
  93
  94   private buildInnerListQuery () {
  95     this.buildWhere()
  96     this.buildInnerListSelect()
  97
  98     this.innerQuery = `${this.innerSelect} ` +
  99       `FROM "videoComment" AS "VideoCommentModel" ` +
 100       `${this.innerJoins} ` +
 101       `${this.innerWhere} ` +
 102       `${this.getOrder()} ` +
 103       `${this.getInnerLimit()}`
 104   }
 105
 106   // ---------------------------------------------------------------------------
 107
 108   private buildCountQuery () {
 109     this.buildWhere()
 110
 111     this.query = `SELECT COUNT(*) AS "total" ` +
 112       `FROM "videoComment" AS "VideoCommentModel" ` +
 113       `${this.innerJoins} ` +
 114       `${this.innerWhere}`
 115   }
 116
 117   // ---------------------------------------------------------------------------
 118
 119   private buildWhere () {
 120     let where: string[] = []
 121
 122     if (this.options.videoId) {
 123       this.replacements.videoId = this.options.videoId
 124
 125       where.push('"VideoCommentModel"."videoId" = :videoId')
 126     }
 127
 128     if (this.options.threadId) {
 129       this.replacements.threadId = this.options.threadId
 130
 131       where.push('("VideoCommentModel"."id" = :threadId OR "VideoCommentModel"."originCommentId" = :threadId)')
 132     }
 133
 134     if (this.options.accountId) {
 135       this.replacements.accountId = this.options.accountId
 136
 137       where.push('"VideoCommentModel"."accountId" = :accountId')
 138     }
 139
 140     if (this.options.videoChannelId) {
 141       this.buildVideoChannelJoin()
 142
 143       this.replacements.videoChannelId = this.options.videoChannelId
 144
 145       where.push('"Account->VideoChannel"."id" = :videoChannelId')
 146     }
 147
 148     if (this.options.blockerAccountIds) {
 149       this.buildVideoChannelJoin()
 150
 151       where = where.concat(this.getBlockWhere('VideoCommentModel', 'Video->VideoChannel'))
 152     }
 153
 154     if (this.options.isThread === true) {
 155       where.push('"VideoCommentModel"."inReplyToCommentId" IS NULL')
 156     }
 157
 158     if (this.options.notDeleted === true) {
 159       where.push('"VideoCommentModel"."deletedAt" IS NULL')
 160     }
 161
 162     if (this.options.isLocal === true) {
 163       this.buildAccountJoin()
 164
 165       where.push('"Account->Actor"."serverId" IS NULL')
 166     } else if (this.options.isLocal === false) {
 167       this.buildAccountJoin()
 168
 169       where.push('"Account->Actor"."serverId" IS NOT NULL')
 170     }
 171
 172     if (this.options.onLocalVideo === true) {
 173       this.buildVideoJoin()
 174
 175       where.push('"Video"."remote" IS FALSE')
 176     } else if (this.options.onLocalVideo === false) {
 177       this.buildVideoJoin()
 178
 179       where.push('"Video"."remote" IS TRUE')
 180     }
 181
 182     if (this.options.onPublicVideo === true) {
 183       this.buildVideoJoin()
 184
 185       where.push(`"Video"."privacy" = ${VideoPrivacy.PUBLIC}`)
 186     }
 187
 188     if (this.options.videoAccountOwnerId) {
 189       this.buildVideoChannelJoin()
 190
 191       this.replacements.videoAccountOwnerId = this.options.videoAccountOwnerId
 192
 193       where.push(`"Video->VideoChannel"."accountId" = :videoAccountOwnerId`)
 194     }
 195
 196     if (this.options.search) {
 197       this.buildVideoJoin()
 198       this.buildAccountJoin()
 199
 200       const escapedLikeSearch = this.sequelize.escape('%' + this.options.search + '%')
 201
 202       where.push(
 203         `(` +
 204           `"VideoCommentModel"."text" ILIKE ${escapedLikeSearch} OR ` +
 205           `"Account->Actor"."preferredUsername" ILIKE ${escapedLikeSearch} OR ` +
 206           `"Account"."name" ILIKE ${escapedLikeSearch} OR ` +
 207           `"Video"."name" ILIKE ${escapedLikeSearch} ` +
 208         `)`
 209       )
 210     }
 211
 212     if (this.options.searchAccount) {
 213       this.buildAccountJoin()
 214
 215       const escapedLikeSearch = this.sequelize.escape('%' + this.options.searchAccount + '%')
 216
 217       where.push(
 218         `(` +
 219           `"Account->Actor"."preferredUsername" ILIKE ${escapedLikeSearch} OR ` +
 220           `"Account"."name" ILIKE ${escapedLikeSearch} ` +
 221         `)`
 222       )
 223     }
 224
 225     if (this.options.searchVideo) {
 226       this.buildVideoJoin()
 227
 228       const escapedLikeSearch = this.sequelize.escape('%' + this.options.searchVideo + '%')
 229
 230       where.push(`"Video"."name" ILIKE ${escapedLikeSearch}`)
 231     }
 232
 233     if (where.length !== 0) {
 234       this.innerWhere = `WHERE ${where.join(' AND ')}`
 235     }
 236   }
 237
 238   private buildAccountJoin () {
 239     if (this.built.accountJoin) return
 240
 241     this.innerJoins += ' LEFT JOIN "account" "Account" ON "Account"."id" = "VideoCommentModel"."accountId" ' +
 242       'LEFT JOIN "actor" "Account->Actor" ON "Account->Actor"."id" = "Account"."actorId" ' +
 243       'LEFT JOIN "server" "Account->Actor->Server" ON "Account->Actor"."serverId" = "Account->Actor->Server"."id" '
 244
 245     this.built.accountJoin = true
 246   }
 247
 248   private buildVideoJoin () {
 249     if (this.built.videoJoin) return
 250
 251     this.innerJoins += ' LEFT JOIN "video" "Video" ON "Video"."id" = "VideoCommentModel"."videoId" '
 252
 253     this.built.videoJoin = true
 254   }
 255
 256   private buildVideoChannelJoin () {
 257     if (this.built.videoChannelJoin) return
 258
 259     this.buildVideoJoin()
 260
 261     this.innerJoins += ' LEFT JOIN "videoChannel" "Video->VideoChannel" ON "Video"."channelId" = "Video->VideoChannel"."id" '
 262
 263     this.built.videoChannelJoin = true
 264   }
 265
 266   private buildAvatarsJoin () {
 267     if (this.options.selectType !== 'api' && this.options.selectType !== 'feed') return ''
 268     if (this.built.avatarJoin) return
 269
 270     this.joins += `LEFT JOIN "actorImage" "Account->Actor->Avatars" ` +
 271       `ON "VideoCommentModel"."Account.Actor.id" = "Account->Actor->Avatars"."actorId" ` +
 272         `AND "Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}`
 273
 274     this.built.avatarJoin = true
 275   }
 276
 277   // ---------------------------------------------------------------------------
 278
 279   private buildListSelect () {
 280     const toSelect = [ '"VideoCommentModel".*' ]
 281
 282     if (this.options.selectType === 'api' || this.options.selectType === 'feed') {
 283       this.buildAvatarsJoin()
 284
 285       toSelect.push(this.tableAttributes.getAvatarAttributes())
 286     }
 287
 288     if (this.options.includeReplyCounters === true) {
 289       toSelect.push(this.getTotalRepliesSelect())
 290       toSelect.push(this.getAuthorTotalRepliesSelect())
 291     }
 292
 293     this.select = this.buildSelect(toSelect)
 294   }
 295
 296   private buildInnerListSelect () {
 297     let toSelect = [ this.tableAttributes.getVideoCommentAttributes() ]
 298
 299     if (this.options.selectType === 'api' || this.options.selectType === 'feed') {
 300       this.buildAccountJoin()
 301       this.buildVideoJoin()
 302
 303       toSelect = toSelect.concat([
 304         this.tableAttributes.getVideoAttributes(),
 305         this.tableAttributes.getAccountAttributes(),
 306         this.tableAttributes.getActorAttributes(),
 307         this.tableAttributes.getServerAttributes()
 308       ])
 309     }
 310
 311     this.innerSelect = this.buildSelect(toSelect)
 312   }
 313
 314   // ---------------------------------------------------------------------------
 315
 316   private getBlockWhere (commentTableName: string, channelTableName: string) {
 317     const where: string[] = []
 318
 319     const blockerIdsString = createSafeIn(
 320       this.sequelize,
 321       this.options.blockerAccountIds,
 322       [ `"${channelTableName}"."accountId"` ]
 323     )
 324
 325     where.push(
 326       `NOT EXISTS (` +
 327         `SELECT 1 FROM "accountBlocklist" ` +
 328         `WHERE "targetAccountId" = "${commentTableName}"."accountId" ` +
 329         `AND "accountId" IN (${blockerIdsString})` +
 330       `)`
 331     )
 332
 333     where.push(
 334       `NOT EXISTS (` +
 335         `SELECT 1 FROM "account" ` +
 336         `INNER JOIN "actor" ON account."actorId" = actor.id ` +
 337         `INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ` +
 338         `WHERE "account"."id" = "${commentTableName}"."accountId" ` +
 339         `AND "serverBlocklist"."accountId" IN (${blockerIdsString})` +
 340       `)`
 341     )
 342
 343     return where
 344   }
 345
 346   // ---------------------------------------------------------------------------
 347
 348   private getTotalRepliesSelect () {
 349     const blockWhereString = this.getBlockWhere('replies', 'videoChannel').join(' AND ')
 350
 351     return `(` +
 352       `SELECT COUNT("replies"."id") FROM "videoComment" AS "replies" ` +
 353       `LEFT JOIN "video" ON "video"."id" = "replies"."videoId" ` +
 354       `LEFT JOIN "videoChannel" ON "video"."channelId" = "videoChannel"."id" ` +
 355       `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ` +
 356         `AND "deletedAt" IS NULL ` +
 357         `AND ${blockWhereString} ` +
 358     `) AS "totalReplies"`
 359   }
 360
 361   private getAuthorTotalRepliesSelect () {
 362     return `(` +
 363       `SELECT COUNT("replies"."id") FROM "videoComment" AS "replies" ` +
 364       `INNER JOIN "video" ON "video"."id" = "replies"."videoId" ` +
 365       `INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ` +
 366       `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" AND "replies"."accountId" = "videoChannel"."accountId"` +
 367     `) AS "totalRepliesFromVideoAuthor"`
 368   }
 369
 370   private getOrder () {
 371     if (!this.options.sort) return ''
 372
 373     const orders = getCommentSort(this.options.sort)
 374
 375     return 'ORDER BY ' + orders.map(o => `"${o[0]}" ${o[1]}`).join(', ')
 376   }
 377
 378   private getLimit () {
 379     if (!this.options.count) return ''
 380
 381     this.replacements.limit = this.options.count
 382
 383     return `LIMIT :limit `
 384   }
 385
 386   private getInnerLimit () {
 387     if (!this.options.count) return ''
 388
 389     this.replacements.limit = this.options.count
 390     this.replacements.offset = this.options.start || 0
 391
 392     return `LIMIT :limit OFFSET :offset `
 393   }
 394 }