Negative Inner Join Mongodb

Question

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

I've 2 tables

Questions:

• _id

• question

• answers

• right_answer

PlayedQuestions:

• _id

• userId

• questionId

• createdAt

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!

Answer 1

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.