Mongoose / MongoDB -在其他文档中通过Array查询

Question

是否有一种方法使查询依赖于另一个模式中的另一个文档?

假设我有一个拥有项目列表的用户，并且我想仅在提供的用户在其项目列表中具有projectId时才通过ID查找项目。

项目架构(从文档扩展):

const projectSchema = new Schema<ProjectSchema, ProjectModel>(
{
name: { type: String, required: true, trim: true },
description: { type: String, default: '', trim: true },
},
{
collection: 'project',
timestamps: true,
versionKey: false,
toJSON: { virtuals: true },
toObject: { virtuals: true },
},
);

用户架构(从文档扩展):

const userSchema = new Schema<UserSchema, UserModel>(
{
email: { type: String, unique: true, required: true, trim: true },
projectIds: { type: [Schema.Types.ObjectId], default: [], ref: 'Project' },
},
{ collection: 'user', timestamps: true, versionKey: false },
);

所以我想得到有ID的项目，在数据库中是在用户的projectIds数组内。

export async function findProjectForUser(user: User, id: string) {
// get the user from DB by user id (security reasons)
const dbUser = await UserModel.findById(user.id);
if(!dbUser) return undefined; // 401
// check if user has id in projectId array
const hasProject = dbUser.projectIds.find(pId => pId === id);
if(!hasProject) return undefined // 401
// fetch project from db by id
const project = await ProjectModel.findById(id);

// => can I merge the two queries into one?

return project
}

Answer 1

您可以使用聚合和查找

db.users.aggregate([    
{ $match: { _id: user.id }},
{ $unwind: "$projectIds"},
{ $lookup: { 
from: ProjectModel.collection.name,
as: "project",
pipeline: [
{ $match: { _id: id }}
]
}}
])
.map(doc => doc.project);