aboutsummaryrefslogtreecommitdiffhomepage
path: root/server
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2023-01-12 11:11:30 +0100
committerChocobozzz <me@florianbigard.com>2023-01-12 11:11:41 +0100
commit0b96a0fb77cee07abac185fb1fb704388498631b (patch)
tree5aa81598d5a1a32646c7c7af66be6e169a79d949 /server
parent3f3530c3dbc5339b4cba53710b8568b4ad39152e (diff)
downloadPeerTube-0b96a0fb77cee07abac185fb1fb704388498631b.tar.gz
PeerTube-0b96a0fb77cee07abac185fb1fb704388498631b.tar.zst
PeerTube-0b96a0fb77cee07abac185fb1fb704388498631b.zip
Optimize again comments list sql query
Diffstat (limited to 'server')
-rw-r--r--server/models/shared/sort.ts2
-rw-r--r--server/models/video/sql/comment/video-comment-list-query-builder.ts45
-rw-r--r--server/tests/api/videos/video-comments.ts7
3 files changed, 38 insertions, 16 deletions
diff --git a/server/models/shared/sort.ts b/server/models/shared/sort.ts
index 77e84dcf4..a9a093099 100644
--- a/server/models/shared/sort.ts
+++ b/server/models/shared/sort.ts
@@ -49,7 +49,7 @@ function getCommentSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]):
49 49
50 if (field === 'totalReplies') { 50 if (field === 'totalReplies') {
51 return [ 51 return [
52 [ Sequelize.literal('"totalReplies"'), direction ], 52 [ 'totalReplies', direction ],
53 lastSort 53 lastSort
54 ] 54 ]
55 } 55 }
diff --git a/server/models/video/sql/comment/video-comment-list-query-builder.ts b/server/models/video/sql/comment/video-comment-list-query-builder.ts
index 3960f6b13..6d752d4a4 100644
--- a/server/models/video/sql/comment/video-comment-list-query-builder.ts
+++ b/server/models/video/sql/comment/video-comment-list-query-builder.ts
@@ -44,6 +44,7 @@ export class VideoCommentListQueryBuilder extends AbstractRunQuery {
44 44
45 private innerSelect = '' 45 private innerSelect = ''
46 private innerJoins = '' 46 private innerJoins = ''
47 private innerLateralJoins = ''
47 private innerWhere = '' 48 private innerWhere = ''
48 49
49 private readonly built = { 50 private readonly built = {
@@ -59,6 +60,10 @@ export class VideoCommentListQueryBuilder extends AbstractRunQuery {
59 private readonly options: ListVideoCommentsOptions 60 private readonly options: ListVideoCommentsOptions
60 ) { 61 ) {
61 super(sequelize) 62 super(sequelize)
63
64 if (this.options.includeReplyCounters && !this.options.videoId) {
65 throw new Error('Cannot include reply counters without videoId')
66 }
62 } 67 }
63 68
64 async listComments <T extends Model> () { 69 async listComments <T extends Model> () {
@@ -97,6 +102,7 @@ export class VideoCommentListQueryBuilder extends AbstractRunQuery {
97 this.innerQuery = `${this.innerSelect} ` + 102 this.innerQuery = `${this.innerSelect} ` +
98 `FROM "videoComment" AS "VideoCommentModel" ` + 103 `FROM "videoComment" AS "VideoCommentModel" ` +
99 `${this.innerJoins} ` + 104 `${this.innerJoins} ` +
105 `${this.innerLateralJoins} ` +
100 `${this.innerWhere} ` + 106 `${this.innerWhere} ` +
101 `${this.getOrder()} ` + 107 `${this.getOrder()} ` +
102 `${this.getInnerLimit()}` 108 `${this.getInnerLimit()}`
@@ -284,11 +290,6 @@ export class VideoCommentListQueryBuilder extends AbstractRunQuery {
284 toSelect.push(this.tableAttributes.getAvatarAttributes()) 290 toSelect.push(this.tableAttributes.getAvatarAttributes())
285 } 291 }
286 292
287 if (this.options.includeReplyCounters === true) {
288 toSelect.push(this.getTotalRepliesSelect())
289 toSelect.push(this.getAuthorTotalRepliesSelect())
290 }
291
292 this.select = this.buildSelect(toSelect) 293 this.select = this.buildSelect(toSelect)
293 } 294 }
294 295
@@ -307,6 +308,14 @@ export class VideoCommentListQueryBuilder extends AbstractRunQuery {
307 ]) 308 ])
308 } 309 }
309 310
311 if (this.options.includeReplyCounters === true) {
312 this.buildTotalRepliesSelect()
313 this.buildAuthorTotalRepliesSelect()
314
315 toSelect.push('"totalRepliesFromVideoAuthor"."count" AS "totalRepliesFromVideoAuthor"')
316 toSelect.push('"totalReplies"."count" AS "totalReplies"')
317 }
318
310 this.innerSelect = this.buildSelect(toSelect) 319 this.innerSelect = this.buildSelect(toSelect)
311 } 320 }
312 321
@@ -344,26 +353,32 @@ export class VideoCommentListQueryBuilder extends AbstractRunQuery {
344 353
345 // --------------------------------------------------------------------------- 354 // ---------------------------------------------------------------------------
346 355
347 private getTotalRepliesSelect () { 356 private buildTotalRepliesSelect () {
348 const blockWhereString = this.getBlockWhere('replies', 'videoChannel').join(' AND ') 357 const blockWhereString = this.getBlockWhere('replies', 'videoChannel').join(' AND ')
349 358
350 return `(` + 359 // Help the planner by providing videoId that should filter out many comments
351 `SELECT COUNT("replies"."id") FROM "videoComment" AS "replies" ` + 360 this.replacements.videoId = this.options.videoId
352 `LEFT JOIN "video" ON "video"."id" = "replies"."videoId" ` + 361
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 ` +
353 `LEFT JOIN "videoChannel" ON "video"."channelId" = "videoChannel"."id" ` + 365 `LEFT JOIN "videoChannel" ON "video"."channelId" = "videoChannel"."id" ` +
354 `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ` + 366 `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ` +
355 `AND "deletedAt" IS NULL ` + 367 `AND "deletedAt" IS NULL ` +
356 `AND ${blockWhereString} ` + 368 `AND ${blockWhereString} ` +
357 `) AS "totalReplies"` 369 `) "totalReplies" ON TRUE `
358 } 370 }
359 371
360 private getAuthorTotalRepliesSelect () { 372 private buildAuthorTotalRepliesSelect () {
361 return `(` + 373 // Help the planner by providing videoId that should filter out many comments
362 `SELECT COUNT("replies"."id") FROM "videoComment" AS "replies" ` + 374 this.replacements.videoId = this.options.videoId
363 `INNER JOIN "video" ON "video"."id" = "replies"."videoId" ` + 375
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 ` +
364 `INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ` + 379 `INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ` +
365 `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" AND "replies"."accountId" = "videoChannel"."accountId"` + 380 `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" AND "replies"."accountId" = "videoChannel"."accountId"` +
366 `) AS "totalRepliesFromVideoAuthor"` 381 `) "totalRepliesFromVideoAuthor" ON TRUE `
367 } 382 }
368 383
369 private getOrder () { 384 private getOrder () {
diff --git a/server/tests/api/videos/video-comments.ts b/server/tests/api/videos/video-comments.ts
index e077cbf73..e35500b0b 100644
--- a/server/tests/api/videos/video-comments.ts
+++ b/server/tests/api/videos/video-comments.ts
@@ -169,6 +169,13 @@ describe('Test video comments', function () {
169 expect(body.data[2].totalReplies).to.equal(0) 169 expect(body.data[2].totalReplies).to.equal(0)
170 }) 170 })
171 171
172 it('Should list the and sort them by total replies', async function () {
173 const body = await command.listThreads({ videoId: videoUUID, sort: 'totalReplies' })
174
175 expect(body.data[2].text).to.equal('my super first comment')
176 expect(body.data[2].totalReplies).to.equal(3)
177 })
178
172 it('Should delete a reply', async function () { 179 it('Should delete a reply', async function () {
173 await command.delete({ videoId, commentId: replyToDeleteId }) 180 await command.delete({ videoId, commentId: replyToDeleteId })
174 181