+ static async listAndCountByVideoForAP (video: MVideoImmutable, start: number, count: number, t?: Transaction) {
+ const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({
+ videoId: video.id,
+ isVideoOwned: video.isOwned()
+ })
+
+ const query = {
+ order: [ [ 'createdAt', 'ASC' ] ] as Order,
+ offset: start,
+ limit: count,
+ where: {
+ videoId: video.id,
+ accountId: {
+ [Op.notIn]: Sequelize.literal(
+ '(' + buildBlockedAccountSQL(blockerAccountIds) + ')'
+ )
+ }
+ },
+ transaction: t
+ }
+
+ return Promise.all([
+ VideoCommentModel.count(query),
+ VideoCommentModel.findAll<MComment>(query)
+ ]).then(([ total, data ]) => ({ total, data }))
+ }
+
+ static async listForFeed (parameters: {
+ start: number
+ count: number
+ videoId?: number
+ accountId?: number
+ videoChannelId?: number
+ }): Promise<MCommentOwnerVideoFeed[]> {
+ const serverActor = await getServerActor()
+ const { start, count, videoId, accountId, videoChannelId } = parameters
+
+ const whereAnd: WhereOptions[] = buildBlockedAccountSQLOptimized(
+ '"VideoCommentModel"."accountId"',
+ [ serverActor.Account.id, '"Video->VideoChannel"."accountId"' ]
+ )
+
+ if (accountId) {
+ whereAnd.push({
+ accountId
+ })
+ }
+
+ const accountWhere = {
+ [Op.and]: whereAnd
+ }
+
+ const videoChannelWhere = videoChannelId ? { id: videoChannelId } : undefined
+
+ const query = {
+ order: [ [ 'createdAt', 'DESC' ] ] as Order,
+ offset: start,
+ limit: count,
+ where: {
+ deletedAt: null,
+ accountId: accountWhere
+ },
+ include: [
+ {
+ attributes: [ 'name', 'uuid' ],
+ model: VideoModel.unscoped(),
+ required: true,
+ where: {
+ privacy: VideoPrivacy.PUBLIC
+ },
+ include: [
+ {
+ attributes: [ 'accountId' ],
+ model: VideoChannelModel.unscoped(),
+ required: true,
+ where: videoChannelWhere
+ }
+ ]
+ }
+ ]
+ }
+
+ if (videoId) query.where['videoId'] = videoId
+
+ return VideoCommentModel
+ .scope([ ScopeNames.WITH_ACCOUNT ])
+ .findAll(query)
+ }
+
+ static listForBulkDelete (ofAccount: MAccount, filter: { onVideosOfAccount?: MAccountId } = {}) {
+ const accountWhere = filter.onVideosOfAccount
+ ? { id: filter.onVideosOfAccount.id }
+ : {}
+
+ const query = {
+ limit: 1000,
+ where: {
+ deletedAt: null,
+ accountId: ofAccount.id
+ },
+ include: [
+ {
+ model: VideoModel,
+ required: true,
+ include: [
+ {
+ model: VideoChannelModel,
+ required: true,
+ include: [
+ {
+ model: AccountModel,
+ required: true,
+ where: accountWhere
+ }
+ ]
+ }
+ ]
+ }
+ ]
+ }
+
+ return VideoCommentModel
+ .scope([ ScopeNames.WITH_ACCOUNT ])
+ .findAll(query)
+ }
+
+ static async getStats () {
+ const totalLocalVideoComments = await VideoCommentModel.count({
+ include: [
+ {
+ model: AccountModel,
+ required: true,
+ include: [
+ {
+ model: ActorModel,
+ required: true,
+ where: {
+ serverId: null
+ }
+ }
+ ]
+ }
+ ]
+ })
+ const totalVideoComments = await VideoCommentModel.count()
+
+ return {
+ totalLocalVideoComments,
+ totalVideoComments
+ }
+ }
+
+ static listRemoteCommentUrlsOfLocalVideos () {
+ const query = `SELECT "videoComment".url FROM "videoComment" ` +
+ `INNER JOIN account ON account.id = "videoComment"."accountId" ` +
+ `INNER JOIN actor ON actor.id = "account"."actorId" AND actor."serverId" IS NOT NULL ` +
+ `INNER JOIN video ON video.id = "videoComment"."videoId" AND video.remote IS FALSE`
+
+ return VideoCommentModel.sequelize.query<{ url: string }>(query, {
+ type: QueryTypes.SELECT,
+ raw: true
+ }).then(rows => rows.map(r => r.url))
+ }
+
+ static cleanOldCommentsOf (videoId: number, beforeUpdatedAt: Date) {
+ const query = {
+ where: {
+ updatedAt: {
+ [Op.lt]: beforeUpdatedAt
+ },
+ videoId,
+ accountId: {
+ [Op.notIn]: buildLocalAccountIdsIn()
+ },
+ // Do not delete Tombstones
+ deletedAt: null
+ }
+ }
+
+ return VideoCommentModel.destroy(query)
+ }
+
+ getCommentStaticPath () {
+ return this.Video.getWatchStaticPath() + ';threadId=' + this.getThreadId()
+ }
+