How can I find a document by comparing the arrays of two other documents-CodePudding

I have a collection called games and each document stores the id of the user that created it.

{
    id:"someId",
    createdBy:"idOfUser1"
}

Then I have a collection of users which have an array of blocked users

User 1

{
    id:"someId",
    blockedUsers: ["idOfUser2"]
}

User 2

{
    id:"someId",
    blockedUsers: ["idOfUser1"]
}

So now I want to do a query on games and only find the games where users haven't blocked eachother. How can I do this, without doing tons of reads?

I dont want to fetch a random game, then fetch the users and hope they haven't blocked eachother to then have to pull another random game

I thought maybe just having a seperate collection called matchmaking where I would store the following

{
  gameId: "someId";
  createdBy: "idOfUser1";
  blockedUserIds: ["idOfUser2"];
}

But this way I could only check with ArrayContains if the game host hasn't blocked me.

I could use a WhereIn filter to pass my blocked users and compare it with the "createdBy" but I can only pass 10 elements when using a in filter.

What are my options?

CodePudding user response：

Basic answer: NoSQL. Don't store information in a DRY manner; instead, have a "single source of truth" for data, but COPY the information to where you need it. In your case, copy the "blocked user" data from the User structures to the "Game structures"

Especially with NoSQL, it tends to be best to think about what questions you want to "ask" your database, and what answers would be useful. Then build your database to match.