How can I get all the orders from the seller and then sort them by the previous date to the present?

This is what I get for all transactions, when I Order.find(),

[
    {
        "_id": "636357777877c919bb2cfa45",
        "products": [
            {
                "productId": "636355a07877c919bb2cdfdc",
                "quantity": 1,
                "sellerId": "636355187877c919bb2cdf1f",
                "_id": "636357777877c919bb2cfa46"
            }
        ],
        "amount": 100,
        "createdAt": "2022-10-03T05:53:59.997Z",
     
    },
    {
        "_id": "636357da7877c919bb2d035f",
        "products": [
            {
                "productId": "636357387877c919bb2cf494",
                "quantity": 1,
                "sellerId": "636355187877c919bb2cdf1f",
                "_id": "636357da7877c919bb2d0360"
            }
        ],
        "amount": 100,
        "createdAt": "2022-11-03T05:55:38.858Z",
        "updatedAt": "2022-11-03T05:55:38.858Z",
        "__v": 0
    },
    {
        "_id": "636367d2407816df5f589bc6",
        "products": [
            {
                "productId": "63635acf7877c919bb2d3d95",
                "quantity": 1,
                "sellerId": "636355187877c919bb2cdf1f",
                "_id": "636367d2407816df5f589bc7"
            }
        ],
        "amount": 20,
        "createdAt": "2022-11-03T07:03:46.350Z",
    }
]

So what I'm trying to get here is the total amount of the seller during a certain month. For example, I have 3 orders here, one of them is from October, while the other two are from November.

So the output should be.

October = 100
November = 120

I tried the code below, but I received an empty array

This is my URL

http://localhost:5000/api/order/previousSales/636355187877c919bb2cdf1f

router.get('/previousSales/:id', async (req,res) => {
    const {id} = req.params
    const date = new Date();
    const lastMonth = new Date(date.setMonth(date.getMonth() - 1));
    const previousMonth = new Date(new Date().setMonth(lastMonth.getMonth() - 1)); 

    try {
      const order = await Order.aggregate([
        {
          $match: {
            createdAt: {gte: previousMonth}, ...(id && {
              products: {$elemMatch: {id}}
            })

          }
        },
        {
          $project: {
            month: { $month: "$createdAt" },
            sales: "$amount",
          },
        },
        {
          $group: {
            _id: "$month",
            total: { $sum: "$sales" },
          },
        },
        
      ])  
      res.status(200).json(order)
    } catch (error) {
      res.status(400).json({message: error.message})
    }

  })

EDIT

I forgot to mention that the Object Id in URL belongs to the sellerId inside the product array.

CodePudding user response：

You can change your $match step to this:

  {$match: {
      createdAt: {$gte: previousMonth},
      products: {$elemMatch: {sellerId: id}}
  }},

Since currently you are looking for the field id but you want to look for the field sellerId.

See how it works on the playground example

CodePudding user response：

Try to change your query to:

const matchId = id ? { 'products._id': id } : { 'products._id': { $ne: undefined } }
const order = await Order.aggregate([
  {
    $match: {
      createdAt: {
        $gte: previousMonth.toISOString(),
      },
      matchId
    },
  },
  {
    $project: {
      month: {
        $month: {
          $dateFromString: {
            dateString: '$createdAt',
          },
        },
      },
      sales: '$amount',
    },
  },
  {
    $group: {
      _id: '$month',
      total: {
        $sum: '$sales',
      },
    },
  },
]);

Link to playground