查询至少有1个关联,但返回全部

本教程将介绍查询至少有1个关联,但返回全部的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

查询至少有1个关联,但返回全部 教程 第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

好了关于查询至少有1个关联,但返回全部的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。