node.js - Mongoose 获取与条件的关系计数

Question

我有两个架构

车辆架构:

const VehicleSchema = new Schema({
title: {
    type: String,
    required: true
},
price: {
    type: Number,
    required: true
},
);
VehicleSchema.virtual('booking', {
    ref: 'Booking',
    localField: '_id',
    foreignField: 'vehicle',
    options: {sort: {created_at: 1}}
});
export default mongoose.model('Vehicle', VehicleSchema);

预订架构:

const BookingSchema = new Schema({
start_at:{
  type:Date,
  required:true
},
end_at:{
    type:Date,
    required:true
},
status: {
    type: String,
    enum: ["APPROVED", "REJECTED",],
    default: "REJECTED"
},
vehicle:{
    type: Schema.Types.ObjectId,
    ref: 'Vehicle'
},
});
export default mongoose.model('Booking', BookingSchema);

每辆车辆都有多个预订

我需要获取所有状态为拒绝和批准状态的车辆:

 [
     {
     "title":"vehicle_1",
     "price":2500,
     "rejected_count":10
     "approved_count":55
     },{ 
     "title":"vehicle_2",
     "price":2500,
     "rejected_count":15
     "approved_count":5
     },{ 
     "title":"vehicle_3",
     "price":2500,
     "rejected_count":1
     "approved_count":30
     },{ 
     "title":"vehicle_4",
     "price":2500,
     "rejected_count":5
     "approved_count":15
     },
]

Answer 1

您可以使用以下聚合

Vehicle.aggregate([
  { "$lookup": {
    "from": Booking.collection.name,
    "let": { "vehicle": "$_id" },
    "pipeline": [
      { "$match": {
        "$expr": { "$eq": [ "$vehicle", "$$vehicle" ] },
        "status": "APPROVED"
      }}
    ],
    "as": "approved"
  }},
  { "$lookup": {
    "from": Booking.collection.name,
    "let": { "vehicle": "$_id" },
    "pipeline": [
      { "$match": {
        "$expr": { "$eq": [ "$vehicle", "$$vehicle" ] },
        "status": "REJECTED"
      }}
    ],
    "as": "rejected"
  }},
  { "$project": {
    "rejected_count": { "$size": "$rejected" },
    "approved_count": { "$size": "$approved" },
    "title": 1,
    "price": 1
  }}
])