node.js - Mongoose 聚合:基于 Model.associated_Model.associated_Model.field 过滤返回错误结果
问题描述
我有三个模型User
、Profile
和Institution
。
用户
const UserSchema = new Schema({
name: {
type: Schema.Types.String,
},
profile: {
type: Schema.Types.ObjectId,
ref: "profiles",
},
});
轮廓
const ProfileSchema = new Schema({
institution: {
type: Schema.Types.ObjectId,
ref: "institutions",
},
});
机构
const InstitutionSchema = new Schema({
name: {
type: String,
required: true,
},
});
我正在尝试获取用户列表WHERE
机构 = 机构名称:
User.aggregate([
{
$lookup: {
from: "profiles",
let: { profiles_id: "$profile" },
pipeline: [
{
$lookup: {
from: "institutions",
pipeline: [
{
$match: { name: institution_name },
},
],
as: "institution",
},
},
{ $unwind: "$institution" },
],
as: "profile",
},
},
{ $unwind: "$profile" },
{
$project: {
name: "$name",
institution: "$profile.institution.name",
},
},
]);
出于某种奇怪的原因,这是返回所有用户的列表,但它用我使用的过滤器值替换了机构字段institution_name
。知道如何解决这个问题吗?
解决方案
查询中的主要错误是您没有根据某些条件执行连接。在第一个$lookup
withprofiles
集合中,您传递profile
为profiles_id
但未在管道中使用它。second $lookup
with的情况institutions
也是如此。
尝试这个:
const institution_name = "Institute 1";
db.users.aggregate([
{
$lookup: {
from: "profiles",
let: { profiles_id: "$profile" },
pipeline: [
{
$match: {
// Join condition.
$expr: { $eq: ["$_id", "$$profiles_id"] }
}
},
{
$lookup: {
from: "institutions",
let: { institution_id: "$institution" },
pipeline: [
{
$match: {
name: institution_name,
// Join condition.
$expr: { $eq: ["$_id", "$$institution_id"] }
}
}
],
as: "institution"
}
},
{ $unwind: "$institution" },
],
as: "profile",
}
},
{ $unwind: "$profile" },
{
$project: {
name: "$name",
institution: "$profile.institution.name"
}
}
]);
输出:
/* 1 createdAt:3/13/2021, 6:19:07 PM*/
{
"_id" : ObjectId("604cb4c36b2dcb17e8b152b8"),
"name" : "Dheemanth Bhat",
"institution" : "Institute 1"
},
/* 2 createdAt:3/13/2021, 6:19:07 PM*/
{
"_id" : ObjectId("604cb4c36b2dcb17e8b152b9"),
"name" : "Ahmed Ghrib",
"institution" : "Institute 1"
}
测试数据:
users
收藏:
/* 1 createdAt:3/13/2021, 6:19:07 PM*/
{
"_id" : ObjectId("604cb4c36b2dcb17e8b152b8"),
"name" : "Dheemanth Bhat",
"profile" : ObjectId("604cb4b16b2dcb17e8b152b5")
},
/* 2 createdAt:3/13/2021, 6:19:07 PM*/
{
"_id" : ObjectId("604cb4c36b2dcb17e8b152b9"),
"name" : "Ahmed Ghrib",
"profile" : ObjectId("604cb4b16b2dcb17e8b152b6")
},
/* 3 createdAt:3/13/2021, 6:19:07 PM*/
{
"_id" : ObjectId("604cb4c36b2dcb17e8b152ba"),
"name" : "Alex Rider",
"profile" : ObjectId("604cb4b16b2dcb17e8b152b7")
}
profiles
收藏:
/* 1 createdAt:3/13/2021, 6:18:49 PM*/
{
"_id" : ObjectId("604cb4b16b2dcb17e8b152b5"),
"institution" : ObjectId("604cb49a6b2dcb17e8b152b2")
},
/* 2 createdAt:3/13/2021, 6:18:49 PM*/
{
"_id" : ObjectId("604cb4b16b2dcb17e8b152b6"),
"institution" : ObjectId("604cb49a6b2dcb17e8b152b2")
},
/* 3 createdAt:3/13/2021, 6:18:49 PM*/
{
"_id" : ObjectId("604cb4b16b2dcb17e8b152b7"),
"institution" : ObjectId("604cb49a6b2dcb17e8b152b3")
},
/* 4 createdAt:3/13/2021, 6:18:49 PM*/
{
"_id" : ObjectId("604cb4b16b2dcb17e8b152b8"),
"institution" : ObjectId("604cb49a6b2dcb17e8b152b4")
}
institutions
收藏:
/* 1 createdAt:3/13/2021, 6:18:26 PM*/
{
"_id" : ObjectId("604cb49a6b2dcb17e8b152b2"),
"name" : "Institute 1"
},
/* 2 createdAt:3/13/2021, 6:18:26 PM*/
{
"_id" : ObjectId("604cb49a6b2dcb17e8b152b3"),
"name" : "Institute 2"
},
/* 3 createdAt:3/13/2021, 6:18:26 PM*/
{
"_id" : ObjectId("604cb49a6b2dcb17e8b152b4"),
"name" : "Institute 3"
}
推荐阅读
- ms-word - 如何按 R Markdown 中的页码将对象交叉引用到 Microsoft Word?
- lit-element - 当插槽在 lit-html 中没有子节点时做一些事情
- ruby-on-rails - 如何在多个模块上使用相同的类名?
- javascript - 如何动态创建n列?
- django - collectstatic 不会忽略指定的文件
- mysql - SQL 查询速度慢且不使用索引
- android - 如何将数据从一个活动中的单选框传递到另一个活动中的一系列复选框
- javascript - 如何强制 :fullscreen 伪类遵循与 :not(:fullscreen) 相同的规则?
- java - 如何检查 ImageInputStream 是否已经关闭?
- github - 计算 Github API 文件 SHA