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 · Accepted 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

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

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

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

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