Fix offset/limit on some SQL queries :facepalm:

[github/Chocobozzz/PeerTube.git] / server / models / video / video-comment.ts

diff --git a/server/models/video/video-comment.ts b/server/models/video/video-comment.ts
index bf8da924d5555ee5d06911941100f5ee47881c7d..86766a5d1edc841c49139e966fe0ac9ae8443bcb 100644 (file)
--- a/server/models/video/video-comment.ts
+++ b/server/models/video/video-comment.ts
@@ -307,15 +307,15 @@ export class VideoCommentModel extends Model<VideoCommentModel> {
     const query = {
       order: [ [ 'createdAt', order ] ],
       where: {
-        [ Sequelize.Op.or ]: [
-          { id: comment.getThreadId() },
-          { originCommentId: comment.getThreadId() }
-        ],
         id: {
+          [ Sequelize.Op.in ]: Sequelize.literal('(' +
+            'WITH RECURSIVE children (id, "inReplyToCommentId") AS ( ' +
+            'SELECT id, "inReplyToCommentId" FROM "videoComment" WHERE id = ' + comment.id + ' UNION ' +
+            'SELECT p.id, p."inReplyToCommentId" from "videoComment" p ' +
+            'INNER JOIN children c ON c."inReplyToCommentId" = p.id) ' +
+            'SELECT id FROM children' +
+          ')'),
           [ Sequelize.Op.ne ]: comment.id
-        },
-        createdAt: {
-          [ Sequelize.Op.lt ]: comment.createdAt
         }
       },
       transaction: t
@@ -326,6 +326,42 @@ export class VideoCommentModel extends Model<VideoCommentModel> {
       .findAll(query)
   }
 
+  static listAndCountByVideoId (videoId: number, start: number, count: number, t?: Sequelize.Transaction, order: 'ASC' | 'DESC' = 'ASC') {
+    const query = {
+      order: [ [ 'createdAt', order ] ],
+      offset: start,
+      limit: count,
+      where: {
+        videoId
+      },
+      transaction: t
+    }
+
+    return VideoCommentModel.findAndCountAll(query)
+  }
+
+  static listForFeed (start: number, count: number, videoId?: number) {
+    const query = {
+      order: [ [ 'createdAt', 'DESC' ] ],
+      offset: start,
+      limit: count,
+      where: {},
+      include: [
+        {
+          attributes: [ 'name' ],
+          model: VideoModel.unscoped(),
+          required: true
+        }
+      ]
+    }
+
+    if (videoId) query.where['videoId'] = videoId
+
+    return VideoCommentModel
+      .scope([ ScopeNames.WITH_ACCOUNT ])
+      .findAll(query)
+  }
+
   static async getStats () {
     const totalLocalVideoComments = await VideoCommentModel.count({
       include: [