javascript - 获取嵌套的BelongsToMany关联

Question

所以我有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});

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

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! 的消息

这很奇怪的原因是我必须通过另一个关系模型将一个模型与一个关系模型关联起来。

感谢您提供的任何帮助!

Answer 1

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

数据库定义:没有变化

数据库关系:

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 ... */
      ]
    }
  ]
}