查询至少有1个关联,但返回全部
我已经创建了此记录,您可以看到它有2个标记-tag1和tag2
{
"id": "d87de1d9-b048-4867-92fb-a84dca59c87e",
"name": "Test Name",
"tags": [
{
"id": "fa0ca8fd-eff4-4e58-8bb0-a1ef726f01d4",
"name": "tag1",
"organizationId": "d87de1d9-b048-4867-92fb-a84dca59c87e",
"updatedAt": "2018-12-05T18:53:56.867Z",
"createdAt": "2018-12-05T18:53:56.867Z"
},
{
"id": "66e758af-9907-4278-8c4f-f8fb2bf9aea9",
"name": "tag2",
"organizationId": "d87de1d9-b048-4867-92fb-a84dca59c87e",
"updatedAt": "2018-12-05T18:53:56.867Z",
"createdAt": "2018-12-05T18:53:56.867Z"
}
],
"updatedAt": "2018-12-05T18:53:56.860Z",
"createdAt": "2018-12-05T18:53:56.860Z"
}
我要编写一个查询,该查询查找包含tag1
的组织,并返回包含所有标记的整个组织。
我当前有此查询,它只返回与查询匹配的标记记录,而不是返回所有标记。
db.organization.findAll({
include: {
model: db.tag,
where: { name: 'tag1' }
}
})
并产生此结果
[
{
"id": "3d03d74e-82ec-485e-aa29-abe9e8b0f0e9",
"name": "Test Name",
"createdAt": "2018-12-05T19:29:40.685Z",
"updatedAt": "2018-12-05T19:29:40.685Z",
"tags": [
{
"id": "75dc9cd2-5e20-4aa6-b86e-cbaa2c896d57",
"name": "tag1", <-- NOTE THAT ONLY TAG1 IS IN THE RESULTS EVEN THOUGH THERE SHOULD BE ANOTHER TAG OBJECT RETURNED
"createdAt": "2018-12-05T19:29:40.694Z",
"updatedAt": "2018-12-05T19:29:40.694Z",
"organizationId": "3d03d74e-82ec-485e-aa29-abe9e8b0f0e9"
}
]
}
]
如何编写查询来执行此操作?
解决方案
我搜索了很多关于此主题的信息,我认为最有效的方法(在本例中)是建立一个用于过滤的基本关联和一个用于接收数据的关联
关联文件
// This is for filtering
organization.hasMany(tag, {
foreignKey: 'organizationId',
});
// This one is for receiving data after filtering
organization.hasMany(tag, {
foreignKey: 'organizationId',
as: 'tags',
});
控制器
const results = await db.organization.findAll({
include: [{
model: db.tag,
where: { name: 'tag1' },
attributes: [], // remove 'Tag' property from results
}, {
model: db.tag,
as: 'tags',
}],
});
console.log(results);
console.log(results);
将返回:
[
{
"id": "...",
"name": "Organization Test Name",
"createdAt": "...",
"updatedAt": "...",
// "Tag": [ // This property was removed by 'attributes: []'
// {
// "id": "...",
// "name": "tag1",
// "organizationId": "..."
// "createdAt": "...",
// "updatedAt": "...",
// },
// ],
"tags": [
{
"id": "...",
"name": "tag1",
"organizationId": "..."
"createdAt": "...",
"updatedAt": "...",
},
{
"id": "...",
"name": "tag1",
"organizationId": "..."
"createdAt": "...",
"updatedAt": "...",
}
]
}
]
GitHub的部分资源:Can't exclude association's fields from select statement in sequelize #3664
相关文章