javascript - 获取嵌套 BelongsToMany 关联

所以我有 Projects 在 Investigators 中有 Roles 并提交 Effort 作为 Role 中的 BudgetPeriod (听起来很愚蠢，不是吗)。我不知道哪些关键字实际上适合搜索此内容。所以这里是:

const Project = seq.define('Project', {/*...*/});
const Investigator = seq.define('Investigator', {/*...*/});
const BudgetPeriod = seq.define('BudgetPeriod', {/*...*/});
const Role = seq.define('Role', {
  id:           {
    type:          DataTypes.INTEGER,
    primaryKey:    true,
    autoIncrement: true
  },
  role:         {
    type:      DataTypes.ENUM,
    /*...*/
  },
  /*... Other role related info...*/
});
const Effort = seq.define('Effort', {
  id:           {
    type:          DataTypes.INTEGER,
    primaryKey:    true,
    autoIncrement: true
  },
  budgetedAY: {
    type:         DataTypes.INTEGER,
    allowNull:    false,
    defaultValue: 0
  },
  /*... other effort related info...*/
});

好的，现在关系:

// Each Project can have many budget periods, BP knows its Project
Project.hasMany(BudgetPeriod, {as: 'budgetPeriods'});

// Each Project has many Investigators, which have a Role on Many projects
Project.belongsToMany(Investigator, {as: 'investigators', through: db.Role});

// Each Investigator, in their role, commits Effort toward the Projects during a BudgetPeriod
Role.belongsToMany(BudgetPeriod, {as: 'effort', through: db.Effort});

在我的查询中，我正在获取一个项目并获取其相关的 BudgetPeriods 和调查员/Angular 色。 我不知道如何获得 Effort，因为它与关系模型 Roles 而不是 Investigator 相关联，并且 Role 根据 sequelize 与 Project 没有直接关联。

Project.findById(projectId, {
  include: [
    {model: Investigator, as: 'investigators'},
    {model: BudgetPeriod, as: 'budgetPeriods'},
  ],
  order:   [
    [{model: BudgetPeriod, as: 'budgetPeriods'}, 'period', 'ASC']
  ]
}).then(res => console.log(res))

// Produces something like
{
  budgetPeriods: [/*...*/],
  investigators: [
    {
      id:1,
      name:'abc',
      Role: {
        id: 1,
        role: 'PI'
      }
    }
  ]
}

我不知道如何在此查询中获得 Effort。我尝试将 {model: Investigator, as: 'investigators'}, 更改为 {model: Investigator, as: 'investigators', include: [{model: Effort, as: 'effort'}]}, 和其他变体。我还尝试将 {model: BudgetPeriod, as: 'effort'} 和其他变体放在根 include 上，但这些都会导致类似 BudgetPeriod (effort) is not associated to Investigator! 的消息

之所以如此奇怪，是因为我必须通过另一个关系模型将模型与关系模型相关联。

感谢您提供的任何帮助!

最佳答案

由于我认为不会有答案，因此我重构了数据库关系。对于那些有类似问题的人，这是我的改变。

数据库定义:无变化

数据库关系:

Project.hasMany(BudgetPeriod, {as: 'budgetPeriods'});
Project.belongsToMany(Investigator, {as: 'investigators', through: db.Role});
Investigator.belongsToMany(BudgetPeriod, {as: 'effort', through: db.Effort});

由于每个调查员在每个项目中可能只有一个 Angular 色，因此可以安全地假设调查员在预算期间所做的每项工作都是由该 Angular 色完成的。

这将我的查询更改为

Project.findById(projectId, {
  include: [
    {model: Investigator, as: 'investigators',
      include: [{model: BudgetPeriod, as: 'effort', where: {ProjectId: projectId}}]},
    {model: BudgetPeriod, as: 'budgetPeriods'},
  ],
  order:   [
    [{model: BudgetPeriod, as: 'budgetPeriods'}, 'period', 'ASC']
  ]
}).then(res => console.log(res))

// Produces something like
{
  budgetPeriods: [/*...*/],
  investigators: [
    {
      id:1,
      name:'abc',
      Role: {
        id: 1,
        role: 'PI'
      },
      Effort: [
        /* ... Effort ... */
      ]
    }
  ]
}

关于javascript - 获取嵌套 BelongsToMany 关联，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39925569/