hagopj13 / node-express-boilerplate

A boilerplate for building production-ready RESTful APIs using Node.js, Express, and Mongoose
MIT License
6.94k stars 2.04k forks source link

Help needed with aggregation and pagination #212

Open rounakcodes opened 2 years ago

rounakcodes commented 2 years ago

I am new to mongodb. This works for me:

const queryCallsByUserId = async (filter, options) => {
  const results = await Call.aggregate([
    { $lookup: { from: 'patients', localField: 'patientId', foreignField: '_id', as: 'patientDetails' } },
  ]);
  return results;
};

However, I don't get any fields related to pagination.

If I use only Call.paginate(), it returns results with pagination. But I don't know how to have paginate working after performing the above aggregation. Thanks.

kallyas commented 2 years ago

Pagination is already taken care of, if you take a look at this file paginate.plugin.js

To make it work, you have to add it to your schema

const mongoose = require('mongoose');]
const { toJSON, paginate } = require('./plugins');

const fooSchema = mongoose.Schema({
...
})

// add plugin that converts mongoose to json
fooSchema.plugin(toJSON);
// add plugin for pagination
fooSchema.plugin(paginate);

In your frontend you can call the api with pagination params like so GET http://localhost:5000/api/v1/foo?page=1&limit=10

The result will be something like this

{
limit: 10
page: 1
results: [{}...]
totalPages: 1
totalResults: 10
}
rounakcodes commented 2 years ago

Yes. Correct. That is what I meant when I said "If I use only Call.paginate(), it returns results with pagination". The problem is when I use the aggregation Call.aggregate that I get results which are not paginated. How to apply pagination to results returned by Call.aggregate?

(PS: I also checked the results using callsWithAgg?page=1&limit=10 but there was no change )

Shidooo commented 2 years ago

I face a similar problem (by using toJSON), I also using an aggregation function. It seems that plugins attached to a schema are not taken when calling an aggregation function.

Unfortunately, I can't find anything about this in the mongodb or mongoose documentation.

rounakcodes commented 2 years ago

Not exactly pagination substitute but I got away for now doing:

const queryCalls = async (filter, { limit = 10, page = 1 }) => {
  const results = await Call.aggregate([
      { $lookup: { from: 'patients', localField: 'patientId', foreignField: '_id', as: 'patientDetails' } },
      {
        $setWindowFields: {
          output: { totalCount: { $count: {} } },
        },
      },
      { $skip: (page - 1) * limit || 0 },
      { $limit: Number(limit) },
  ]);
  return results;
}

@Shidooo Would passing your aggregate results to JSON.stringify() work?

Shidooo commented 2 years ago

Glad that you found a workaround for your problem!

Yes, it works but it is not what I expect.

According to the implementation of toJSON, , it

I could certainly change it manually (not very nice if you have several aggregate functions), but isn't there a more elegant solution? Since the toJSON plugin exists, It would be better to use it (instead of rewriting it just for aggregation functions...)

Shidooo commented 2 years ago

My workaround, hoping to find a more elegant solution.

   // rename _id to id
    {
      $addFields: {
        id: '$_id',
      },
    },
    // remove _id and __v
    {
      $project: {
        __v: 0,
        _id: 0,
      },
    },
sagardspeed2 commented 1 year ago

Is there any update on this ?

kallyas commented 1 year ago

My work around, is as follows

const queryCalls = async (filter, { limit = 10, page = 1 }) => {
  // Calculate the offset based on the current page and limit
  const offset = (page - 1) * limit;

  // Perform the aggregation pipeline
  const results = await Call.aggregate([
    { $lookup: { from: 'patients', localField: 'patientId', foreignField: '_id', as: 'patientDetails' } },
    // You can add more pipeline stages here if needed
    // ...

    // Skip the specified number of documents based on the offset
    { $skip: offset },

    // Limit the number of documents to the specified limit
    { $limit: Number(limit) },
  ]);

  // You might want to add more logic here, depending on the use case

  return results;
}