Schema:
const orderSchema = mongoose.Schema(
{
orderStatus: {
type: String,
enum: ["pending", "preparing", "completed", "declined"],
default: "pending",
},
products: [
{
product: {
productId: {
type: mongoose.Schema.Types.ObjectId,
ref: "Product",
},
productName: String,
productPrice: Number,
categoryName: String,
},
quantity: {
type: Number,
required: true,
}
},
],
totalPrice: { type: Number },
acceptDeclineTime: {
type: Date,
default: Date.now,
},
}
);
I want a yearly sales report with number of orders accepted and declined, with total price for each.
I tried:
orderSchema.aggregate(
[
{
$unwind: {
path: "$products",
},
},
{
$group: {
_id: { $year: { date: "$acceptDeclineTime", timezone: "+03:00" } },
totalCompletedPrice: {
$sum: {
$cond: [{ $eq: ["$orderStatus", "completed"] }, "$totalPrice", 0],
},
},
totalDeclinedPrice: {
$sum: {
$cond: [{ $eq: ["$orderStatus", "declined"] }, "$totalPrice", 0],
},
},
totalItems: {
$sum: "$products.quantity",
},
completedSales: {
$sum: {
$cond: [{ $eq: ["$orderStatus", "completed"] }, "$products.quantity", 0],
},
},
cancelledSales: {
$sum: {
$cond: [{ $eq: ["$orderStatus", "declined"] }, "$products.quantity", 0],
},
},
},
},
]);
But the price calculation is wrong as the $unwind
stage duplicates total price of products which will be problematic on the $sum
operation.
2
Answers
Simple solution suggested by pugro from reddit was to divide the total price by the size of the products array before $unwind operation then when recombined it'll add up.
I think you have to group two times, similar to this: