const score_schema = mongoose.Schema(
  {
    tester_id: {
      type: mongoose.Schema.Types.ObjectId,
      ref: "user",
      required: true,
    },
    test_id: {
      type: mongoose.Schema.Types.ObjectId,
      ref: "test",
      required: true,
    },
    score: {
      type: Number,
      required: true,
    },
  },
  {
    collection: `score`,
    timestamps: true,
  }
);

query 1:

A user is given. First a query to the score model is made to find all tests that this user has taken. This will result in an arbitrary number of tests and the score of each test.

query 2:

Another query is made to the score model to find all documents where the test_id matches the test_id returned from the above query and where the score of each test is greater than or equal to the score of the test_id returned from the query above.

This essentially means that the arbitrary number of documents returned from query 1 will be the number of filter conditions to query 2.

Problem:

Can the above 2 queries be combined into 1 query and thus one round trip to the MongoDB API? If not, then a solution of 2 separate queries is also acceptable.

const dummy_data = [
  {
    tester_id: "1",
    test_id: "1",
    score: 40
  },
  {
    tester_id: "1",
    test_id: "2",
    score: 50
  },
   {
    tester_id: "1",
    test_id: "3",
    score: 70
  },
  {
    tester_id: "2",
    test_id: "1",
    score: 50
  },
  {
    tester_id: "3",
    test_id: "2",
    score: 20
  },
   {
    tester_id: "3",
    test_id: "3",
    score: 60
  },
  {
    tester_id: "7",
    test_id: "5",
    score: 40
  },
  {
    tester_id: "8",
    test_id: "4",
    score: 50
  },
   {
    tester_id: "9",
    test_id: "4",
    score: 70
  },
]

CodePudding user response：

Here comes a detailed aggregation that handles the data

If I understood all the requirements correctly

[{$facet: {
 tests_per_tester: [
  {
   $match: {
/***   select the tester here **/
    tester_id: '1'
   }
  },
  {
   $group: {
    _id: '$tester_id',
    tests: {
     $push: '$$ROOT'
    }
   }
  }
 ],
 scores_by_test: [
  {
   $group: {
    _id: '$test_id',
    scores: {
     $push: '$score'
    }
   }
  }
 ]
}}, {$unwind: {
 path: '$tests_per_tester'
}}, {$unwind: {
 path: '$tests_per_tester.tests'
}}, {$unwind: {
 path: '$scores_by_test'
}}, {$unwind: {
 path: '$scores_by_test.scores'
}}, {$match: {
 $expr: {
  $and: [
   {
    $eq: [
     '$tests_per_tester.tests.test_id',
     '$scores_by_test._id'
    ]
   },
   {
    $gte: [
     '$tests_per_tester.tests.score',
     '$scores_by_test.scores'
    ]
   }
  ]
 }
}}]

The $facet step divides the data into two categories: testers and tests, while the rest of the query is just a series of $unwinds and a $match to give the desired results.

Mongo playground

CodePudding user response：

You can use the aggregate query with $lookup

db.collection.aggregate([
  {
    $match: {
      "tester_id": "1"
    }
  },
  {
    "$lookup": {
      "from": "collection",
      "let": {
        "score": "$score"
      },
      "localField": "tester_id",
      "foreignField": "test_id",
      "pipeline": [
        {
          $match: {
            $expr: {
              $gte: [
                "$score",
                "$$score"
              ]
            }
          }
        }
      ],
      "as": "result"
    }
  }
])

Output:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "result": [
      {
        "_id": ObjectId("5a934e000102030405000000"),
        "score": 40,
        "test_id": "1",
        "tester_id": "1"
      },
      {
        "_id": ObjectId("5a934e000102030405000003"),
        "score": 50,
        "test_id": "1",
        "tester_id": "2"
      }
    ],
    "score": 40,
    "test_id": "1",
    "tester_id": "1"
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "result": [
      {
        "_id": ObjectId("5a934e000102030405000003"),
        "score": 50,
        "test_id": "1",
        "tester_id": "2"
      }
    ],
    "score": 50,
    "test_id": "2",
    "tester_id": "1"
  },
  {
    "_id": ObjectId("5a934e000102030405000002"),
    "result": [],
    "score": 70,
    "test_id": "3",
    "tester_id": "1"
  }
]

Playground