猫鼬聚合与组查找 [英] Mongoose Aggregate with lookup with group

问题描述

我的发票模型如下

{
...

"itemDetails": [
            {
                "item":  "593a1a01bbb00000043d9a4c",
                "purchasingPrice": 100,
                "sellingPrice": 150,
                "qty": 200,
                "_id": "59c39c2a5149560004173a05",
                "discount": 0
            }
        ],
        "payments": [],
...

}

项目详细信息项目是一个引用项目集合的对象ID.

Item details Item is an object Id which refers to Item collection.

我想按商品出售，所以我设法按照以下方式解决

I wanted to get sale by Item so I manage to work it out following way

Invoice.aggregate([
    {
      "$unwind": "$itemDetails"
    }, {
      "$group": {
        "_id": "$itemDetails.item",
        "qty": {
          "$sum": "$itemDetails.qty"
        },
        "value": {
          "$sum": {
            "$multiply": [
              "$itemDetails.qty", {
                "$subtract": ["$itemDetails.sellingPrice", "$itemDetails.discount"]
              }
            ]
          }
        },
        "avarageSellingPrice": {
          "$avg": {
            "$subtract": ["$itemDetails.sellingPrice", "$itemDetails.discount"]
          }
        }
      }
    }
  ]).then(salesFigures => {
    res.status(200).json(salesFigures);
  });

这将产生以下结果.

[
    {
        "_id": "59c89c6d68dffc0004f42a86",
        "qty": 50,
        "value": 1250,
        "avarageSellingPrice": 25
    },
    {
        "_id": "593a4bbfbbb00000043d9a54",
        "qty": 320,
        "value": 48000,
        "avarageSellingPrice": 150
    }
]

我需要从项目集合中获取带有项目名称的结果，例如

I need to get result with item Name from Item collection like

[
    {
        "_id": "59c89c6d68dffc0004f42a86",
        "itemName": "Item one",
        "qty": 50,
        "value": 1250,
        "avarageSellingPrice": 25
    },
    {
        "_id": "593a4bbfbbb00000043d9a54",
        "itemName": "Item Two",
        "qty": 320,
        "value": 48000,
        "avarageSellingPrice": 150
    }
]

我考虑过在组之前使用查找功能，但是没有用.

I thought of using lookup before group but didn't work.

从发票集合中抽样文档

{
    "_id": {
        "$oid": "59c39c2a5149560004173a04"
    },
    "customer": {
        "$oid": "5935013832f9fc0004fa9a16"
    },
    "order": {
        "$oid": "59c1df8393cbba0004a0e956"
    },
    "employee": {
        "$oid": "592d0a6238880f0004637e84"
    },
    "status": "PENDING",
    "deliveryStatus": "PROCESSING",
    "created": {
        "$date": "2017-09-21T11:02:02.675Z"
    },
    "discount": 0,
    "payments": [],
    "itemDetails": [
        {
            "item": {
                "$oid": "593a1a01bbb00000043d9a4c"
            },
            "purchasingPrice": 100,
            "sellingPrice": 150,
            "qty": 200,
            "_id": {
                "$oid": "59c39c2a5149560004173a05"
            },
            "discount": 0
        }
    ],
    "__v": 0
}

项目文档如下所示

{
    "_id": {
        "$oid": "593a1a01bbb00000043d9a4c"
    },
    "itemCode": 1213,
    "itemName": "KIT KAT",
    "status": "active",
    "created": {
        "$date": "2017-06-09T03:46:09.445Z"
    },
    "__v": 0,
    "updated": {
        "$date": "2017-06-21T07:46:31.232Z"
    },
    "purchasingPrice": 100,
    "retailPrice": 140,
    "sellingPrice": 150
}

推荐答案

到目前为止，打the是什么? 如评论中已经提到的，您必须在$group

You got so far, what is the hiccup? As already mentioned in the comment, you'd have to add the $lookup after your $group

{
    "$lookup": {
        from: "Item",
        localField: "_id",
        foreignField: "_id",
        as: "itemName"
    }
}

然后您必须$unwind，因为它是一个数组

Then you'd have to $unwind since it's an array

{
    "$unwind": "$itemName",
}

然后您使用最终的$project检索实际的 itemName

And you use the final $project to retrieve the actual itemName

{
    "$project": {
        _id: 1,
        qty: 1,
        value: 1,
        avarageSellingPrice: 1,
        itemName: "$itemName.itemName"
    }
}

这篇关于猫鼬聚合与组查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！