mongoose sort document by populated field with conditions
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]} }}])
# 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)});