query documents that depend on values of other doc

问题:

Imagine following mongoose model:

const UserSchema = Schema({
  //_id: ObjectId,
  //more fields,
  blockedIds: [{
    type: ObjectId,
    ref: 'User'
  }]
})

What is the most efficient way to get all users that don't match the blockedIds of an user with a certain _id?

A naive way would be to to perform two queries:

User.findById(id).then(user => {
  return User.find({_id: {$nin: user.blockedIds}})
})

Is it possible to use the aggregation framework and $facets to accomplish that in one query?

回答1:

Try non-correlated sub query from 3.6 for your use case.

Something like

User.aggregate(
 [{$lookup:{
   from: "users",
   pipeline:[
    {$match: {_id:mongoose.Types.ObjectId(id)}},
    {$project: {_id:0,blockedIds:1}}
   ],
   as: "noncr"
 }},
 {$match:{
   $expr:{
     $not:[
      {$in:[
        $_id,
        {$arrayElemAt:["$noncr.blockedIds",0]}
      ]}
    ]
  }
}},
{$project:{noncr:0}}]
)

$lookup to pull in the "blockedIds" for input id followed by $match to filter the documents where "_id" is not in list of blockedIds.

$expr allows use of aggregation comparison operators in $match stage.

$arrayElemAt to fetch the first element from $lookup array.

$in to compare the _id against blockedIds.

$project with exclusion to remove the "noncr" field from the final response.

Please note when you test query use the collection name not model or schema name in "from" attribute of look up stage.

回答2:

The aggregation framework in the other answer is useful, but for the sake of completeness, here's an alternate approach. In Redis db designs, you always have to think of accessing the data from "both ways", so you think of inverting results. So that's what we can do here.

Instead of having blockedIds array on user, have a blockedBy:

const UserSchema = Schema({
  blockedBy: [{
    type: ObjectId,
    ref: 'User',
    index: true,
  }]
});

So now you invert the problem and the query becomes trivial:

User.find({ userID: { $nin: blockedBy: } });

In some situations it could be a viable option.