时间戳

首页 > 解决方案 > 我如何计算每个类别的考试

node.js - 我如何计算每个类别的考试

问题描述

我有 3 个收藏。一个用于类别，一个用于考试，一个用于结果。类别如下：

{
  _id: 3276438964823,
  name: 'Category 1',
  companyId: 12345
}

考试集如下：

{
  _id: 482348327,
  categoryId: 3276438964823,
  name: "Computer",
  companyId: 12345
}

结果集合如下：

{
  _id: 482348327,
  exam_id: 482348327
}

我想要的结果是这样的：

[
  {category: 'Categori 1', exam_count: 1, exam_done: 0},
  {category: 'Categori 2', exam_count: 8, exam_done: 5},
  .....
]

现在我已经尝试过这段代码：

Exams.aggregate([
    {
      $match: {
        companyId: { $in: 
            [mongoose.Types.ObjectId(req.userData.companyId)] },
      },
    },
    {
      $lookup: {
        from: "categories",
        let: {
          companyid: "$companyId",
        },
        pipeline: [
          {
            $match: {
              $expr: {
                $and: [
                  {
                    $eq: ["$companyId", "$$companyid"],
                  },
                  {
                    $eq: ["$type", 1],
                  },
                ],
              },
            },
          },
          {
            $project: {
              _id: 1,
              name: 1,
            }
          }
        ],
        as: "categories",
      },
    },
    ])

我现在得到的是这个：

[
{
    "_id": "5ee366cb3fd50f3d388493ac",
    "active": 1,
    "categoryId": "5eb640414ac4b84d944a3639",
    "name": "Test 2",
    "insertDate": "2020-06-12T11:28:11.966Z",
    "__v": 0,
    "companyId": "5f621c2461090c2f40a014d3",
    "categories": [
        {
            "_id": "5f621d3e61090c2f40a014d9",
            "name": "A1,A2,B1,B"
        },
        {
            "_id": "5f621d4b61090c2f40a014db",
            "name": "C1,C"
        }
    ]
},
{
    "_id": "5f5d2eea4387a83088e6f802",
    "active": 1,
    "categoryId": "5f5cf44710cd434344b36a91",
    "name": "Test 2",
    "insertDate": "2020-09-12T20:26:18.294Z",
    "__v": 0,
    "companyId": "5f621c2461090c2f40a014d3",
    "categories": [
        {
            "_id": "5f621d3e61090c2f40a014d9",
            "name": "A1,A2,B1,B"
        },
        {
            "_id": "5f621d4b61090c2f40a014db",
            "name": "C1,C"
        }
    ]
},
]

但这不是我想要的。我想计算我拥有的所有类别的所有考试类别集合

标签： node.jsmongodbaggregate

解决方案

您正在从考试集合中查询并使用类别查找，但它应该从类别中查询并使用考试查找，

  • $lookup带exams收藏
  • $lookup带results收藏
  • $addFeilds使用计算总考试和总成绩$size
Categories.aggregate([
  {
    $lookup: {
      from: "exams",
      localField: "_id",
      foreignField: "categoryId",
      as: "examsCount"
    }
  },
  {
    $lookup: {
      from: "results",
      localField: "examsCount._id",
      foreignField: "exam_id",
      as: "doneExams"
    }
  },
  {
    $addFields: {
      examsCount: { $size: "$examsCount" },
      doneExams: { $size: "$doneExams" }
    }
  }
])

操场

推荐阅读