Resume pagination after specific document-CodePudding

I'm loading user comments on my website. Initially, I only load a subset of comments and show a Load more button which loads the next page and appends it to the end of the comments list.

Loading another page works by using the usual skip and limit aggregation steps in MongoDB:

const aggregationResult = await ResourceCommentModel.aggregate()
    .match({
        resourceId: new mongoose.Types.ObjectId(req.params.resourceId),
        parentCommentId: undefined
    })
    .sort({ 'createdAt': -1 })
    .facet({
        comments: [
            { $skip: (page - 1) * pageSize },
            { $limit: pageSize },
            {
                $lookup: {
                    from: 'resourcecomments',
                    localField: '_id',
                    foreignField: 'parentCommentId',
                    as: 'replies',
                }
            }
        ],
        totalCount: [{ $group: { _id: null, count: { $sum: 1 } } }]
    })
    .exec();

Problem: If new comments have been posted in the meantime, comments from page one have been pushed to page 2, and we are loading duplicate comments (and showing them in the UI).

One option would be to not rely on the page number, but instead start after the last loaded timestamp:

comments: [
    { $match: { createdAt: { $gt: new Date(continueAfterTimestamp) } } },
    { $limit: pageSize },
    {
        $lookup: {
            from: 'resourcecomments',
            localField: '_id',
            foreignField: 'parentCommentId',
            as: 'replies',
        }
    }
],

However, there are 2 problems with this approach:

Although unlikely, if two comments were posted at the exact same millisecond, one of them will not be loaded.
The bigger problem: I'm planning to later allow sorting comments by upvotes, and then I don't have a way to start after the last loaded document anymore.

CodePudding user response：

Use ObjectID instead of the timestamp. It is unique, and has a timestamp inside.

{ $match: { _id: { $gt: new ObjectId(continueAfterID) } } },

For the "bigger problem" add upvotes to the play. You gonna need an index anyway, so make it compound {upvote:-1, _id:1} and pass the latest upvote to the filter:

{ $match: { $or:[
    {$and: [{upvote: continueUpvote}, {_id: { $gt: new ObjectId(continueAfterID) } }] },
    {upvote: {$lt: continueUpvote} }
]}}

Or just dedup results clientside. It's trivial with react, and still easy to do with vanilla js and real DOM.