从2个集合中查找Mongoose,得到错误的和值

gdx19jrr  于 12个月前  发布在  Go
关注(0)|答案(1)|浏览(113)

我有以下收藏
实体:

const Entity = new Schema(
  {
    name: {
      type: String,
      required: true,
    },
    type: {
      type: String,
      required: true,
      enum: ["owner", "customer", "supplier"],
    },

字符串
账号:

const Account = new Schema(
  {
    name: {
      type: String,
      required: true,
    },
    phone: {
      type: String,
      default: "+964",
    },


公关部:

const PR = new Schema(
  {
    number: {
      type: Number,
      default: 0,
    },
    customer: {
      type: Schema.Types.ObjectId,
      ref: "Entity",
    },
    owner: {
      type: Schema.Types.ObjectId,
      ref: "Entity",
    },
    account: {
      type: Schema.Types.ObjectId,
      ref: "Account",
    },
    items: [item.schema]
    ,
total_usd: {
  type: Number,
  default: 0,
},
total_iqd: {
  type: Number,
  default: 0,
},


发票:

const Invoice = new Schema(
  {
    number: {
      type: Number,
      index: true,
      default: 0,
    },
    customer: {
      type: Schema.Types.ObjectId,
      ref: "Entity",
    },
    owner: {
      type: Schema.Types.ObjectId,
      ref: "Entity",
    },
    account: {
      type: Schema.Types.ObjectId,
      ref: "Account",
    },
    total_usd: {
      type: Number,
      default: 0,
    },
    total_iqd: {
      type: Number,
      default: 0,
    },


我需要得到status and year and account分组的PRInvoices集合的total_usdtotal_iqd的和
我正在使用下面的查询,它会产生非常奇怪的结果和数字

await entityModel
    .aggregate([
      { $match: { _id: new mongoose.Types.ObjectId(req.query._id) } },
      {
        $lookup: {
          from: "invoices",
          localField: "_id",
          foreignField: "customer",
          as: "invoices",
        },
      },

      {
        $unwind: "$invoices",
      },
      {
        $lookup: {
          from: "accounts",
          localField: "invoices.account",
          foreignField: "_id",
          pipeline: [{ $project: { _id: 0, name: 1 } }],
          as: "invoices.account",
        },
      },
      { $unwind: "$invoices.account" },
      {
        $lookup: {
          from: "prs",
          localField: "_id",
          foreignField: "customer",
          as: "prs",
        },
      },
      {
        $unwind: "$prs",
      },
      {
        $lookup: {
          from: "accounts",
          localField: "prs.account",
          foreignField: "_id",
          pipeline: [{ $project: { _id: 0, name: 1 } }],
          as: "prs.account",
        },
      },
      { $unwind: "$prs.account" },
      {
        $group: {
          _id: {
            inv_status: "$invoices.status",
            inv_account: "$invoices.account",
            inv_year: { $year: "$invoices.created_at" },
            pr_status: "$prs.status",
            pr_account: "$prs.account",
            pr_year: { $year: "$prs.created_at" },
          },
          inv_sum_usd: { $sum: "$invoices.total_usd" },
          inv_sum_iqd: { $sum: "$invoices.total_iqd" },
          pr_sum_usd: { $sum: "$prs.total_usd" },
          pr_sum_iqd: { $sum: "$prs.total_iqd" },
        },
      },
    ])
    .then((result) => {
      res.send(result);
    })


结果如下:

[
    {
        "_id": {
            "inv_status": "approved",
            "inv_account": {
                "name": "FIIQ"
            },
            "inv_year": 2023,
            "pr_status": "approved",
            "pr_account": {
                "name": "FIIX"
            },
            "pr_year": 2023
        },
        "inv_sum_usd": 480000,
        "inv_sum_iqd": 0,
        "pr_sum_usd": 514440,
        "pr_sum_iqd": 0
    },
    {
        "_id": {
            "inv_status": "approved",
            "inv_account": {
                "name": "FIIQ"
            },
            "inv_year": 2023,
            "pr_status": "approved",
            "pr_account": {
                "name": "FIIQ"
            },
            "pr_year": 2023
        },
        "inv_sum_usd": 144000,
        "inv_sum_iqd": 0,
        "pr_sum_usd": 40700,
        "pr_sum_iqd": 14256000
    },
    {
        "_id": {
            "inv_status": "approved",
            "inv_account": {
                "name": "FIIX"
            },
            "inv_year": 2023,
            "pr_status": "approved",
            "pr_account": {
                "name": "FIIX"
            },
            "pr_year": 2023
        },
        "inv_sum_usd": 2551740,
        "inv_sum_iqd": 0,
        "pr_sum_usd": 2829420,
        "pr_sum_iqd": 0
    },
    {
        "_id": {
            "inv_status": "approved",
            "inv_account": {
                "name": "FIIX"
            },
            "inv_year": 2023,
            "pr_status": "approved",
            "pr_account": {
                "name": "FIIQ"
            },
            "pr_year": 2023
        },
        "inv_sum_usd": 765522,
        "inv_sum_iqd": 0,
        "pr_sum_usd": 223850,
        "pr_sum_iqd": 78408000
    }
]


这是不匹配我的数据?!!(总美元的FIIX = 257220)任何帮助将不胜感激

ne5o7dgx

ne5o7dgx1#

这个问题源于展开后的查找,它像carryout连接一样增加了文档的数量。
例如,假设我们有这个文档:

{a: [1,2,3], b: ['x', 'y', 'z']}

字符串
解压缩a后,我们得到

{ a: 1, b: ['x',  'y', 'z']}
{ a: 2, b: ['x',  'y', 'z']}
{ a: 3, b: ['x',  'y', 'z']}


然后展开b,我们得到

{ a: 1, b: 'x'}
{ a: 1, b: 'y'}
{ a: 1, b: 'z'}
{ a: 2, b: 'x'}
{ a: 2, b: 'y'}
{ a: 2, b: 'z'}
{ a: 3, b: 'x'}
{ a: 3, b: 'y'}
{ a: 3, b: 'z'}


你可以在任何解卷发生之前perform the lookups and zip the looked up fields来解决这个问题。

await entityModel
  .aggregate([{
      $match: {
        _id: new mongoose.Types.ObjectId(req.query._id)
      }
    },
    {
      $lookup: {
        from: "invoices",
        localField: "_id",
        foreignField: "customer",
        as: "invoices",
        pipeline: [{
            $lookup: {
              from: "accounts",
              localField: "account",
              foreignField: "_id",
              pipeline: [{
                $project: {
                  _id: 1,
                  name: 1
                }
              }],
              as: "account"
            }
          },
          {
            $unwind: "$account"
          },
          { // adds a field to mark the collection we looked up from
            "$addFields": {
              "kind": "invoice"
            }
          }
        ]
      }
    },
    {
      $lookup: {
        from: "prs",
        localField: "_id",
        foreignField: "customer",
        as: "prs",
        pipeline: [{
            $lookup: {
              from: "accounts",
              localField: "account",
              foreignField: "_id",
              pipeline: [{
                $project: {
                  _id: 1,
                  name: 1
                }
              }],
              as: "account"
            }
          },
          {
            $unwind: "$account"
          },
          { // adds a field to mark the collection we lookedup from
            "$addFields": {
              "kind": "pr"
            }
          }
        ]
      }
    },
    {
      $project: {
        _id: 1,
        name: 1,
        type: 1,
        docs: {
          $zip: {
            inputs: [
              "$prs",
              "$invoices"
            ],
            useLongestLength: true
          }
        }
      }
    },
    {
      $unwind: "$docs"
    },
    {
      $unwind: "$docs"
    },
    { // We exclude missing document because we set the `useLongestLength` option when zipping invoices and prs.
      $match: {
        docs: {
          $ne: null
        }
      }
    },
    { // The layout of the results are document wise. It should be possible to layout differently.
      $group: {
        _id: {
          status: "$docs.status",
          account: "$docs.account._id",
          kind: "$docs.kind",
          year: {
            $year: "$docs.created_at"
          }
        },
        sum_usd: {
          $sum: "$docs.total_usd"
        },
        sum_iqd: {
          $sum: "$docs.total_iqd"
        }
      }
    }
  ])


您可以通过将组阶段更改为以下方式更改the layout of the results

await entityModel
  .aggregate([{
      $match: {
        _id: new mongoose.Types.ObjectId(req.query._id)
      }
    },
    {
      $lookup: {
        from: "invoices",
        localField: "_id",
        foreignField: "customer",
        as: "invoices",
        pipeline: [{
            $lookup: {
              from: "accounts",
              localField: "account",
              foreignField: "_id",
              pipeline: [{
                $project: {
                  _id: 1,
                  name: 1
                }
              }],
              as: "account"
            }
          },
          {
            $unwind: "$account"
          },
          {
            "$addFields": {
              "kind": "invoice"
            }
          }
        ]
      }
    },
    {
      $lookup: {
        from: "prs",
        localField: "_id",
        foreignField: "customer",
        as: "prs",
        pipeline: [{
            $lookup: {
              from: "accounts",
              localField: "account",
              foreignField: "_id",
              pipeline: [{
                $project: {
                  _id: 1,
                  name: 1
                }
              }],
              as: "account"
            }
          },
          {
            $unwind: "$account"
          },
          {
            "$addFields": {
              "kind": "pr"
            }
          }
        ]
      }
    },
    {
      $project: {
        _id: 1,
        name: 1,
        type: 1,
        docs: {
          $zip: {
            inputs: [
              "$prs",
              "$invoices"
            ],
            useLongestLength: true
          }
        }
      }
    },
    {
      $unwind: "$docs"
    },
    {
      $unwind: "$docs"
    },
    {
      $match: {
        docs: {
          $ne: null
        }
      }
    },
    {
      $group: {
        _id: {
          status: "$docs.status",
          account: "$docs.account._id",
          year: {
            $year: "$docs.created_at"
          }
        },
        pr_sum_usd: {
          $sum: {
            $cond: [{
                $eq: [
                  "$docs.kind",
                  "pr"
                ]
              },
              "$docs.total_usd",
              0
            ]
          }
        },
        pr_sum_iqd: {
          $sum: {
            $cond: [{
                $eq: [
                  "$docs.kind",
                  "pr"
                ]
              },
              "$docs.total_iqd",
              0
            ]
          }
        },
        inv_sum_usd: {
          $sum: {
            $cond: [{
                $eq: [
                  "$docs.kind",
                  "invoice"
                ]
              },
              "$docs.total_usd",
              0
            ]
          }
        },
        inv_sum_iqd: {
          $sum: {
            $cond: [{
                $eq: [
                  "$docs.kind",
                  "invoice"
                ]
              },
              "$docs.total_iqd",
              0
            ]
          }
        }
      }
    }
  ])

相关问题