代码之家 › 专栏 › 技术社区 › Matheus Barem

Mongoose:我需要合计一些字段的累加器

aggregation-framework mongoose mongodb node.js

Matheus Barem · 技术社区 · 6 年前

我有一个查询(聚合),我需要计算一些字段的累加器( value 和 comissionValue )并计算这个查询有多少个寄存器。

我的查询(聚合)

  let findTerm = {
    store: req.body.store,
    status: {
      $in: resultStatusServices
    }
  }
  if (req.body.start) {
    findTerm.scheduleStart = {
      $lte: req.body.start
    };
  }

  if (req.body.period) {
    findTerm.scheduleEnd = {
      $gte: req.body.period
    };
  }

  Schedule.aggregate([{
              $match: findTerm              
            },
            {
              $project: {
                "employee.name": 1,
                "customer.name": 1,
                "service.name": 1,
                "value": 1,
                "scheduleDate": 1,
                "scheduleStart": 1,
                "scheduleEnd": 1,
                "comissionValue": 1,
                "status": 1,
                "paymentMethod": 1
              }
            },
            {
              $group:{
                _id: {
                  "employee.name" : "$employee.name",
                  "customer.name" : "$customer.name",
                  "service.name": "$service.name",
                  "value": "$value",
                  "scheduleDate": "$scheduleDate",
                  "scheduleStart" :"$scheduleStart",
                  "scheduleEnd": "$scheduleEnd",
                  "comissionValue" : "$comissionValue",
                  "status" : "$value",
                  "paymentMethod" : "$paymentMethod"
                },
              }
            },
            {
              $match: findTerm
            },
            {
              $group: {
                _id: {
                  id: "$store"
                },
                totalValue: {
                  $sum: "$value"
                },
                totalServices: {
                  "$sum": 1             
                },
                totalComission: {
                  $sum: "$comissionValue"
                },
                count: {
                  $sum: 1
                }
              }
            },
            {
              $sort: sort
            },
            {
              $skip: req.body.limit * req.body.page
            },
            {
              $limit: req.body.limit
            }

时间表(模型)

 store: {
    type: String,
    required: true
  },
  customer: {
    id: {
      type: String
    },
    name: {
      type: String
    },
    avatar: String,
    phone: {
      type: String
    },
    email: { type: String },
    doc: {
      type: String
    },
  },
  employee: {
    id: {
      type: String,
      required: true
    },
    name: {
      type: String,
      required: true
    },
    avatar: String,
  },
  service: {
    id: {
      type: String
    },
    name: {
      type: String,
      required: true
    },
    filters: [String]
  },
  info: {
    channel: {
      type: String,
      required: true,
      default: 'app'
    },
    id: String,
    name: String
  },
  scheduleDate: {
    type: String,
    required: true
  },
  scheduleStart: {
    type: String,
    required: true
  },
  scheduleEnd: {
    type: String,
    required: true
  },
  value: {
    type: Number
  },
  comissionType: {
    type: String,
    default: '$'
  },
  comissionValue: {
    type: Number,
    default: 0
  },
  status: {
    type: Number,
    required: true
  },
  observation: String,
  paymentMethod: {
    type: Number,
    default: 0
  },

由于此查询,我正在尝试执行以下操作:

[
0:{
comissionValue: 14
customer: {name: "Marcelo"}
employee: {name: "Andy"}
paymentMethod: 0
scheduleDate: "2019-01-01"
scheduleEnd: "2019-01-01 09:30"
scheduleStart: "2019-01-01 09:00"
service: {name: "Barber"}
status: 2
value: 20
_id: "5c26275ffe046d25a07cb466"}
1: {...}
2: {...}
...
],[totalizers: { count: 2, totalServices: 50, totalComission:65}]

我该怎么做,我该怎么做这个累加器?

1 回复 | 直到 6 年前

chridam 6 年前

你可以使用 $facet 为了完成这种类型的查询,因为它允许您在同一组输入文档上运行各种聚合,而不需要多次检索输入文档。第一个方面可以使用具有排序和限制的查询管道,另一个方面将生成聚合和( totalizers )

例如,以下聚合操作将为您提供所需的结果:

Schedule.aggregate([
    { '$match': findTerm },
    { '$facet': {
        'data': [
            { '$project': {
                'employee.name': 1,
                'customer.name': 1,
                'service.name': 1,
                'value': 1,
                'scheduleDate': 1,
                'scheduleStart': 1,
                'scheduleEnd': 1,
                'comissionValue': 1,
                'status': 1,
                'paymentMethod': 1
            } },
            { '$sort': sort },
            { '$skip': req.body.limit * req.body.page },
            { '$limit': req.body.limit }
        ],
        'totalizer': [
            { '$group': {
                '_id': '$store',
                'count': { '$sum': 1 },
                'totalValue': { '$sum': '$value' },
                'totalComission': { '$sum': '$comissionValue' }
            } },
            { '$group': {
                '_id': null,
                'storesCount': { 
                    '$push': {
                        'store': '$_id',
                        'count': '$count'
                    }    
                },
                'totalValue': { '$sum': '$totalValue' },
                'totalServices': { '$sum': '$count' },
                'totalComission': { '$sum': '$totalComission' }
            } }
        ]
    } }
]).exec((err, results) => {
    if (err) handleError(err);
    console.log(results[0]);
})