skip to Main Content

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


  1. Chosen as BEST ANSWER

    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.

    $addFields:{
      totalPrice:{$divide:['$totalPrice',{$size:'$products'}]}
    }
    

  2. I think you have to group two times, similar to this:

    orderSchema.aggregate([
       { $unwind: { path: "$products" } },
       {
          $group: {
             _id: {
                year: { $year: { date: "$acceptDeclineTime", timezone: "+03:00" } },
                orderStatus: "$orderStatus"
             },
             products: { $push: "$products" },
             totalPrice: { $sum: "$totalPrice" }
          }
       },
       {
          $group: {
             _id: "$_id.year",
             ...
          }
       }
    ]);
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search