Express+Mongoose+Aggregation+计算可用库存

Question

我使用的是express js和mongoose，我对这些平台还不熟悉。如果有人能在这方面帮助我，那就太好了。请参考以下数据，我正在寻找类似的输出：

itemsizeId: 609578ca23033e55886e7590, AvailableQuantity: 112
itemsizeId: 609578ca23033e55886e758f, AvailableQuantity: 495

注意：按移动类型分组(向内/向外(。

可用库存：内向-外向

[
{
_id: '609fb1a1a7ed990f30d6cae2',
refId: 'Purchase-1',
itemsizeId: '609578ca23033e55886e7590',
itemId: '609578ca23033e55886e758e',
sizeId: '60950c0ba4982390f8dfed79',
movementdate: '2021-05-15T11:33:52.894Z',
movementtype: 'inward',
movementcategory: 'purchase',
quantity: 100,
id: '609fb1a1a7ed990f30d6cae2',
},
{
_id: '609fb1cba7ed990f30d6cae3',
refId: 'Purchase-2',
itemsizeId: '609578ca23033e55886e7590',
itemId: '609578ca23033e55886e758e',
sizeId: '60950c0ba4982390f8dfed79',
movementdate: '2021-05-15T11:34:35.118Z',
movementtype: 'inward',
movementcategory: 'purchase',
quantity: 20,
id: '609fb1cba7ed990f30d6cae3',
},
{
_id: '609fb1fda7ed990f30d6cae4',
refId: 'Sale-1',
itemsizeId: '609578ca23033e55886e7590',
itemId: '609578ca23033e55886e758e',
sizeId: '60950c0ba4982390f8dfed79',
movementdate: '2021-05-15T11:35:25.068Z',
movementtype: 'outward',
movementcategory: 'sales',
quantity: 5,
id: '609fb1fda7ed990f30d6cae4',
},
{
_id: '609fb255a7ed990f30d6cae5',
refId: 'Purchase-3',
itemsizeId: '609578ca23033e55886e758f',
itemId: '609578ca23033e55886e758e',
sizeId: '60950be9a4982390f8dfed78',
movementdate: '2021-05-15T11:36:53.835Z',
movementtype: 'inward',
movementcategory: 'purchase',
quantity: 500,
id: '609fb255a7ed990f30d6cae5',
},
{
_id: '609fb27ea7ed990f30d6cae6',
refId: 'Sale-2',
itemsizeId: '609578ca23033e55886e758f',
itemId: '609578ca23033e55886e758e',
sizeId: '60950be9a4982390f8dfed78',
movementdate: '2021-05-15T11:37:34.066Z',
movementtype: 'outward',
movementcategory: 'sales',
quantity: 8,
id: '609fb27ea7ed990f30d6cae6',
},
]

我已经试过了，直到下面的代码，我很震惊地完成了它：

const itemStock = await itemStockMovementModel.aggregate([
{
$match: { movementtype: 'inward' },
},
{
$group: {
_id: {
itemsizeId: '$itemsizeId',
},
quantity: { $sum: '$quantity' },
},
},
])

Answer 1

• $group乘以itemsizeId，并根据条件得到inward和outward的数量和
• $subtract至inward-outward

const itemStock = await itemStockMovementModel.aggregate([
{
$group: {
_id: "$itemsizeId",
inward: {
$sum: { $cond: [{ $eq: ["$movementtype", "inward"] }, "$quantity", 0] }
},
outward: {
$sum: { $cond: [{ $eq: ["$movementtype", "outward"] }, "$quantity", 0] }
}
}
},
{
$project: {
quantity: {
$subtract: ["$inward", "$outward"]
}
}
}
])

游乐场

---

如果movementtype、中没有第三种类型，您也可以使用第二种公式

• $groupbyitemsizeId
• 检查条件如果movementtype是inward，则求和quantity，否则求和负quantity

const itemStock = await itemStockMovementModel.aggregate([
{
$group: {
_id: "$itemsizeId",
quantity: {
$sum: {
$cond: [
{ $eq: ["$movementtype", "inward"] },
"$quantity",
{ $subtract: [0, "$quantity"] }
]
}
}
}
}
])

游乐场