aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2020-08-20 08:52:16 +0200
committerChocobozzz <me@florianbigard.com>2020-08-20 11:05:45 +0200
commit1c58423f6c42f494ea6358043bcb5a735b7bd5d7 (patch)
tree501b91e6a36cf95ae0f1ea4816ee9a8207b154ac /server/models
parent1c5c31a1cef7b12d71a799ae9c2a4371647bfee2 (diff)
downloadPeerTube-1c58423f6c42f494ea6358043bcb5a735b7bd5d7.tar.gz
PeerTube-1c58423f6c42f494ea6358043bcb5a735b7bd5d7.tar.zst
PeerTube-1c58423f6c42f494ea6358043bcb5a735b7bd5d7.zip
Optimize comment RSS sql query
Diffstat (limited to 'server/models')
-rw-r--r--server/models/utils.ts25
-rw-r--r--server/models/video/video-comment.ts29
2 files changed, 40 insertions, 14 deletions
diff --git a/server/models/utils.ts b/server/models/utils.ts
index d706d9ea8..6e5522346 100644
--- a/server/models/utils.ts
+++ b/server/models/utils.ts
@@ -129,6 +129,30 @@ function buildBlockedAccountSQL (blockerIds: number[]) {
129 'WHERE "serverBlocklist"."accountId" IN (' + blockerIdsString + ')' 129 'WHERE "serverBlocklist"."accountId" IN (' + blockerIdsString + ')'
130} 130}
131 131
132function buildBlockedAccountSQLOptimized (columnNameJoin: string, blockerIds: number[]) {
133 const blockerIdsString = blockerIds.join(', ')
134
135 return [
136 literal(
137 `NOT EXISTS (` +
138 ` SELECT 1 FROM "accountBlocklist" ` +
139 ` WHERE "targetAccountId" = ${columnNameJoin} ` +
140 ` AND "accountId" IN (${blockerIdsString})` +
141 `)`
142 ),
143
144 literal(
145 `NOT EXISTS (` +
146 ` SELECT 1 FROM "account" ` +
147 ` INNER JOIN "actor" ON account."actorId" = actor.id ` +
148 ` INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ` +
149 ` WHERE "account"."id" = ${columnNameJoin} ` +
150 ` AND "serverBlocklist"."accountId" IN (${blockerIdsString})` +
151 `)`
152 )
153 ]
154}
155
132function buildServerIdsFollowedBy (actorId: any) { 156function buildServerIdsFollowedBy (actorId: any) {
133 const actorIdNumber = parseInt(actorId + '', 10) 157 const actorIdNumber = parseInt(actorId + '', 10)
134 158
@@ -201,6 +225,7 @@ function searchAttribute (sourceField?: string, targetField?: string) {
201 225
202export { 226export {
203 buildBlockedAccountSQL, 227 buildBlockedAccountSQL,
228 buildBlockedAccountSQLOptimized,
204 buildLocalActorIdsIn, 229 buildLocalActorIdsIn,
205 SortType, 230 SortType,
206 buildLocalAccountIdsIn, 231 buildLocalAccountIdsIn,
diff --git a/server/models/video/video-comment.ts b/server/models/video/video-comment.ts
index 1d5c7280d..de27b3d87 100644
--- a/server/models/video/video-comment.ts
+++ b/server/models/video/video-comment.ts
@@ -1,6 +1,6 @@
1import * as Bluebird from 'bluebird' 1import * as Bluebird from 'bluebird'
2import { uniq } from 'lodash' 2import { uniq } from 'lodash'
3import { FindOptions, Op, Order, ScopeOptions, Sequelize, Transaction } from 'sequelize' 3import { FindOptions, Op, Order, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize'
4import { 4import {
5 AllowNull, 5 AllowNull,
6 BelongsTo, 6 BelongsTo,
@@ -40,7 +40,7 @@ import {
40import { VideoCommentAbuseModel } from '../abuse/video-comment-abuse' 40import { VideoCommentAbuseModel } from '../abuse/video-comment-abuse'
41import { AccountModel } from '../account/account' 41import { AccountModel } from '../account/account'
42import { ActorModel, unusedActorAttributesForAPI } from '../activitypub/actor' 42import { ActorModel, unusedActorAttributesForAPI } from '../activitypub/actor'
43import { buildBlockedAccountSQL, buildLocalAccountIdsIn, getCommentSort, throwIfNotValid } from '../utils' 43import { buildBlockedAccountSQL, buildBlockedAccountSQLOptimized, buildLocalAccountIdsIn, getCommentSort, throwIfNotValid } from '../utils'
44import { VideoModel } from './video' 44import { VideoModel } from './video'
45import { VideoChannelModel } from './video-channel' 45import { VideoChannelModel } from './video-channel'
46 46
@@ -460,19 +460,20 @@ export class VideoCommentModel extends Model<VideoCommentModel> {
460 const serverActor = await getServerActor() 460 const serverActor = await getServerActor()
461 const { start, count, videoId, accountId, videoChannelId } = parameters 461 const { start, count, videoId, accountId, videoChannelId } = parameters
462 462
463 const accountExclusion = { 463 const whereAnd: WhereOptions[] = buildBlockedAccountSQLOptimized(
464 [Op.notIn]: Sequelize.literal( 464 '"VideoCommentModel"."accountId"',
465 '(' + buildBlockedAccountSQL([ serverActor.Account.id, '"Video->VideoChannel"."accountId"' ]) + ')' 465 [ serverActor.Account.id, '"Video->VideoChannel"."accountId"' ]
466 ) 466 )
467
468 if (accountId) {
469 whereAnd.push({
470 [Op.eq]: accountId
471 })
472 }
473
474 const accountWhere = {
475 [Op.and]: whereAnd
467 } 476 }
468 const accountWhere = accountId
469 ? {
470 [Op.and]: {
471 ...accountExclusion,
472 [Op.eq]: accountId
473 }
474 }
475 : accountExclusion
476 477
477 const videoChannelWhere = videoChannelId ? { id: videoChannelId } : undefined 478 const videoChannelWhere = videoChannelId ? { id: videoChannelId } : undefined
478 479