Mongoose limit offset and count query

Question

Bit of an odd one on query performance    I need to run a query which does a total count of documents  and can also return a result set that can be limited and offset   So  I have 57 documents in total  and the user wants 10 documents offset by 20   I can think of 2 ways of doing this  first is query for all 57 documents  returned as an array   then using array slice return the documents they want  The second option is to run 2 queries  the first one using mongo s native  count  method  then run a second query using mongo s native  limit and  skip aggregators   Which do you think would scale better  Doing it all in one query  or running two separate ones   Edit      1 query var limit   10  var offset   20   Animals find     function  err  animals        if  err            return next err              res send  count  animals length  animals  animals slice offset  limit   offset              2 queries Animals find      limit 10  skip 20  function  err  animals                    if  err            return next err              Animals count     function  err  count            if  err                return next err                      res send  count  count  animals  animals

User · Answer

After having to tackle this issue myself, I would like to build upon user854301's answer.

Mongoose ^4.13.8 I was able to use a function called toConstructor() which allowed me to avoid building the query multiple times when filters are applied. I know this function is available in older versions too but you'll have to check the Mongoose docs to confirm this.

The following uses Bluebird promises:

let schema = Query.find({ name: 'bloggs', age: { $gt: 30 } });

// save the query as a 'template'
let query = schema.toConstructor();

return Promise.join(
    schema.count().exec(),
    query().limit(limit).skip(skip).exec(),

    function (total, data) {
        return { data: data, total: total }
    }
);

Now the count query will return the total records it matched and the data returned will be a subset of the total records.

Please note the () around query() which constructs the query.

User · Answer

I suggest you to use 2 queries    db collection count   will return total number of items  This value is stored somewhere in Mongo and it is not calculated  db collection find   skip 20  limit 10  here I assume you could use a sort by some field  so do not forget to add an index on this field  This query will be fast too    I think that you shouldn t query all items and than perform skip and take  cause later when you have big data you will have problems with data transferring and processing

User · Answer

There is a library that will do all of this for you  check out mongoose-paginate-v2

User · Answer

Instead of using 2 separate queries  you can use aggregate   in a single query  Aggregate  quot  facet quot  can be fetch more quickly  the Total Count and the Data with skip  amp  limit     db collection aggregate              sort                    match                 facet             quot stage1 quot        quot  group quot     id null  count   sum 1                 quot stage2 quot         quot  skip quot   0     quot  limit quot   2                              unwind   quot  stage1 quot               output projection        project           count   quot  stage1 count quot           data   quot  stage2 quot                 output as follows -         count  50       data                                              Also  have a look at https   docs mongodb com manual reference operator aggregation facet

User · Answer

db collection name aggregate           match                      sort            id    -1              facet                 metadata       count   quot total quot               data       skip  1       limit  10       project      quot  id quot  0         add projection here wish you re-shape the docs              Instead of using two queries to find the total count and skip the matched record   facet is the best and optimized way   Match the record Find total count skip the record And also can reshape data according to our needs in the query

[mongodb] Mongoose limit/offset and count query

Examples related to mongodb

Examples related to mongoose