CODEWITHSUNDEEP
mongodbinner-join
Clowning
Clowning

Reputation: 179

Negative Inner Join Mongodb

I'm trying to build an API with a MongoDB but I'm stuck at this point...

I've 2 tables

Questions:

PlayedQuestions:

So assuming this I need to create a request to select a question that a specific user didn't play before based on playedQuestions (questionId and userId) and randomly!

So if you've some ideas to help me to build it, Thank you!

Upvotes: 0

Views: 48

Answers (1)

kevinadi
kevinadi

Reputation: 13775

I don't believe this is a natural thing to do in MongoDB, and will result in inefficient database operations. This is because the two collections are unrelated, so you would need to join them together. Since this is not a typical MongoDB operation, this will be an expensive process.

Further, for every new question, this expensive operation (in MongoDB terms) would need to be repeated again.

As I understand it, you need to take the set difference between all the _id of questions vs. all the questionId for a specific player _id.

Instead of doing the whole operation in MongoDB, I would suggest you to:

  1. When the app starts, do a query for all question _id, and store them in a variable.
  2. When a new user logs in, query for all questionId for the user.
  3. Do a set difference between (1) and (2) in the application, pick one at random, then query that exact question _id from the database.
  4. Update the PlayedQuestions collection.

This way, when the user wants to get the next question, you don't need to do the expensive joins every time. The information was already cached in the application.

Upvotes: 1

Related Questions