CODEWITHSUNDEEP
node.jsmongodbmongoosedatabase-design
Mortada Jafar
Mortada Jafar

Reputation: 3679

mongoose sort document by populated field with conditions

i have two schema

vehicle schema :

const VehicleSchema = new Schema({
title: {
    type: String,
    required: true
},
price: {
    type: Number,
    required: true
},
);
VehicleSchema.virtual('promotions', {
   ref: 'Promotion',
   localField: '_id',
   foreignField: 'vehicle',
   justOne: true
});
export default mongoose.model('Vehicle', VehicleSchema);

Promotion Schema :

const PromotionSchema = new Schema({
    start_at: {
        type: Date,
        required: true
    },
    end_at: {
        type: Date,
        required: true
    },
    vehicle: {
        type: Schema.Types.ObjectId,
        ref: 'Vehicle'
    },
    amount:{
        type:Number,
        required:true
    },
});
export default mongoose.model('Promotion', PromotionSchema);

every vehicle have multi Promotion and one of Promotions is active(start_at less then Date.now and end_at greater then Date.now)

1- i need to get all Vehicles with one Promotion(that now active) and sort its by start_at

2- is it possible to add virtual field with name is_promotion and set it to true if have active Promotion?

note: It is possible that some Vehicle do not have any Promotion

Upvotes: 3

Views: 1098

Answers (2)

s7vr
s7vr

Reputation: 75914

You can use $lookup.

$lookup promotions by vehicle id for a criteria and sort and limit to pick up the latest promotion.

$addFields to add the is_promotion field.

VehicleSchema.aggregate([
  {"$lookup":{
    "from":"Promotion",
    "let":{"_id":"$_id"},
    "pipeline":[
      {"$match":{
        "start_at":{"$lt":Date.now},
        "end_at":{"$gt":Date.now},
        "$expr":{"$eq":["$vehicle","$$_id"]}
      }},
      {"$sort":{"start_at":-1}},
      {"$limit":1}
    ],
    "as":"activepromotion"
  }},
  {"$addFields":{
    "is_promotion":{"$gt":[{"$size":"$activepromotion"},0]}
  }}
])

Upvotes: 3

Naveen kumar
Naveen kumar

Reputation: 1

# Try it to get the start_at of our VehicleSchema, then use find() to search for this in the VehicleSchema field across all PromotionSchema.#

PromotionSchema
.findOne({ start_at: {
        type: Date
    } })
.populate('VehicleSchema') 
.exec(function (err, PromotionSchema) {
  if (err) return handleError(err);
  console.log('The total vehicles %s', PromotionSchema.VehicleSchema.vehicle);
.short(Date)
});

Upvotes: -1

Related Questions