mysql - Mongoosejs : Cleanest way to select rows with `limit` and `where` , 然后获取总数

Question

当我使用 PHP 和 MySQL 时，我需要选择行数，然后计算总数(对于分页，例如maple)，我会执行以下操作:

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

然后

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

简单易行。

最近我开始从事 nodejs/mongo 项目，我想知道使用 mongoose 实现相同目标的最干净的方法是什么。

现在我的代码如下所示:

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);

我不喜欢的是我在 .where('mode').equals('published') 中重复自己。

有更好的方法吗？

Answer 1

似乎您想要对数据进行分页。
检查这个例子:

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);
    });
}

用法:

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
});

附:由于多行代码，这段代码看起来非常庞大。
因此最好有一些全局函数，例如:countRecordsByModel、getRecordsByModel、paginateRecordsByModel，您可以将模型、查询、页面、回调作为参数。
它将缩短您的代码以供将来重用。

使上面的所有代码更加灵活，因此只需通过传递 mongoose 模型作为参数、查询、页码和回调函数来调用 paginateRecordsByModel 即可。

采取这段代码:

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);
    });
}

用法:

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);
});