我正在尝试在我的express-mongo项目中JOIN 3个表格。 我有一个名为Product的表格,就像这样:
Product:
_id:5f92a8dfad47ce1b66d4473b
name:"Freno 1"
createdFrom:5f648f7d642ed7082f5ff91f
category:5f92a00c4637a61a397320a1
description:"Freni di tutti i tipi"
sellingPrice:1050
purchasePrice:350
我需要对求和后的可用数量进行提取,并从其他两个表中减去purchaseorders
和salesorders
的数量。
purchaseorders
_id:5f930c6c6817832c0d5acbb4
items:[
{
_id:5f930c6c6817832c0d5acbb5
product:5f92abaf17ec621c1da4f4f9
quantity:10
purchasingPrice:1500
discount:300
},
{
_id:5f930c6c6817832c0d5acbb6
product:5f92a8dfad47ce1b66d4473b
quantity:7
purchasingPrice:1500
discount:300
}]
salesorders
_id:5f930c6c6817832c0d5acbb4
items:[
{
_id:5f930c6c6817832c0d5acbb5
product:5f92abaf17ec621c1da4f4f9
quantity:3
sellingPrice:1500
discount:300
},
{
_id:5f930c6c6817832c0d5acbb6
product:5f92a8dfad47ce1b66d4473b
quantity:3
sellingPrice:1500
discount:300
}]
采购订单
和销售订单
被设计为在同一订单中包含不同的产品。
我正在尝试将这3个表合并以获得以下结果:
[
{_id: 5f92a8dfad47ce1b66d4473b,
name: "Freno 1",
quantity: 4 },
etc.. with all other products
]
我想要对purchaseorders
的所有数量进行求和并减去salesorders
的数量总和。
我尝试使用以下方式从Product表开始进行聚合:
let result = await Product.aggregate([
{
$lookup: {
from: "purchaseorders",
localField: "_id",
foreignField: "items.product",
as: "purchaseorders"
}
},
{
$lookup: {
from: "salesorders",
localField: "_id",
foreignField: "items.product",
as: "salesorders"
}
},
{
$unwind: "$purchaseorders"
},
{
$unwind: "$purchaseorders.items"
},
{
$unwind: "$salesorders"
},
{
$unwind: "$salesorders.items"
},
{
$group: {
_id: "$_id"
}
}
])
感谢那些尝试帮助我的人!