Mongoosejs: Cleanest way to select rows with `limit` and `where`, then get count of total

Question

When I work with PHP and MySQL and I need to select number of rows and then count total (for pagination, for exmaple) I do something like this:

$rows = DB::getRows('
    SQL_CALC_FOUND_ROWS
    select * 
    from posts 
    where mode = "published" 
    order by `dateCreated` 
    limit '.$limit.'
');

and then

$totalRows = DB::getOne('
    SELECT FOUND_ROWS()
');

Easy and simple.

Recently I started to work on nodejs/mongo project and I wonder what is the cleanest way to achieve the same goal with mongoose.

Now my code looks like this:

var result = {};

var sort = {dateCreated: -1};
var skip = limit * (page - 1);

modelClass.find(function (err, items) {

    result.items = items;

    modelClass.find().where('mode').equals('published').count(function (err, total) {
        result.totalItems = total;
        callback([], result);
    });

}).where('mode').equals('published').sort(sort).limit(limit).skip(skip);

What I do not Like is that I am repeating myself in .where('mode').equals('published').

Is there a better way to do it?

Possible dupe of http://stackoverflow.com/questions/12542620/how-to-make-pagination-with-mongoose — JohnnyHK, Dec 15 '15 at 05:36

num8er · Answer 1 · 2016-06-26T21:58:16.087

Seems like You want paginate data.
Check this example:

var async = require('async');
var _ = require('lodash');

function getData(query, page, callback) { // wrap it inside function to reuse it
    if(page < 1) page = 1; // make sure page number = 1 if it's out of minimal bounds

    var query = _.extend({mode: 'published'}, query); // make it to be able to add additional parameters
    var sort = {dateCreated: -1}; 
    var limit = 50;
    var skip = limit * (page - 1);

    // making 2 parallel calls, not harming readability
    async.parallel([ // array of calls

      // 0. get count by query and send it to parallel result handler
      modelClass
        .find(query)
        .count()
        .exec,

      // 1. get records by defined query and send it parallel handler
      modelClass
        .find(query)
        .sort(sort)
        .limit(limit)
        .skip(skip)
        .exec
    ],

    // parallel result handler - callback, results of each function sent by index in array above
    function(err, results) {
      if(err) {
        return callback(err);
      }     

      // aggregating result in easy to use object 
      var result = {
          count: results[0], // where we get count
          page: page,
          pages: Math.ceil(results[0] / limit), 
          perPage: limit,
          records: results[1] // where we get documents
      };
      callback(null, result);
    });
}

Usage:

getData({}, 2, function(err, result) {
  console.log('Pages:', result.pages, 'Total records:', result.count);
  console.log('Per page:', result.perPage, 'Current page:', result.page);
  console.log('Records:', result.records);
});

getData({publishedBy: 'user-id-here'}, 5, function(err, result) {
  // same example code
});

p.s. this code can be seen as huge because of multiple lines.
so it's better to have some global functions like: countRecordsByModel, getRecordsByModel, paginateRecordsByModel where You push, model, query, page, callback as argument.
It will shorten Your code for future re-use.

Made all of code above to be more flexible, so just call paginateRecordsByModel by passing mongoose model as an argument, query, page number and callback function.

Take this code:

var async = require('async');
var _ = require('lodash');

function countRecordsByModel(modelClass, query, callback) {
  modelClass
    .find(query)
    .count()
    .exec(callback);
}

function getRecordsByModel(modelClass, query, page, callback) {
  if(page < 1) page = 1; 
  var query = _.extend({}, query);

  /* or can extend it with default params like:

  var query = _.extend({deleted: false}, query); // in my databases I have deleted field, to flag documents as deleted, I delete them physically after a month, by garbage cleaner.
  */

  var sort = {dateCreated: -1}; 
  var limit = 50;
  var skip = limit * (page - 1);

  modelClass
    .find(query)
    .sort(sort)
    .limit(limit)
    .skip(skip)
    .exec(callback);
}

function paginateRecordsByModel(modelClass, query, page, callback) {

    // making 2 parallel calls, not harming readability
    async.parallel([ // array of calls

      // 0. get count by query and send it to parallel result handler
      function(done) {
        countRecordsByModel(modelClass, query, done)
      },

      // 1. get records by defined query and send it parallel handler
      function(done) {
        getRecordsByModel(modelClass, query, page, done);
      }

    ],

    // parallel result handler - callback, results of each function sent by index in array above
    function(err, results) {
      if(err) {
        return callback(err);
      }     

      // aggregating result in easy to use object 
      var result = {
          count: results[0], // where we get count
          page: page,
          pages: Math.ceil(results[0] / limit), 
          perPage: limit,
          records: results[1] // where we get documents
      };
      callback(null, result);
    });
}

usage:

paginateRecordsByModel(postsModel, {mode: 'published'}, function(err, result) {
  console.log('Pages:', result.pages, 'Total records:', result.count);
  console.log('Per page:', result.perPage, 'Current page:', result.page);
  console.log('Records:', result.records);
});

Mongoosejs: Cleanest way to select rows with `limit` and `where`, then get count of total

1 Answers1