mysql - 使用 include 在何处进行 Sequelize

Question

我正在使用 sequelize 作为我的后端 ORM。但是当我想要“where”连接表时，我遇到了问题。协会很好，但我不知道如何为“哪里”做。

这是我的代码:

router.get('/id_presta_struct_unit/:id_presta_struct_unit', (req, res) => {
  models.structures.findAll({
    include: {
      required: false,
      model: models.structures_proposer_prestations,
      where: {
        id_presta_struct_unit: req.params.id_presta_struct_unit
      },
      include: {
        model : models.unites_facturation,
      }
    }
  }).then(data => {
    res.writeHead(200, {'Content-Type': 'application/json; charset=utf-8'});
    res.end(JSON.stringify(data));
  });
});

我收到了这个请求

SELECT * FROM structures AS structures LEFT OUTER JOIN structures_proposer_prestations AS structures_proposer_prestations ON 结构.id_structure = structures_proposer_prestations.id_structure AND structures_proposer_prestations.id_presta_struct_unit = '1' 左外连接 unites_facturation 作为 structures_proposer_prestations.unites_facturation 在 structures_proposer_prestations 上。id_unite = structures_proposer_prestations.unites_facturation.id_unite;

但是我想得到

SELECT * FROM structures AS structures LEFT OUTER JOIN structures_proposer_prestations AS structures_proposer_prestations ON 结构.id_structure = structures_proposer_prestations.id_structure 左外连接 unites_facturation AS structures_proposer_prestations.unites_facturation 在 structures_proposer_prestations.id_unite = structures_proposer_prestations.unites_facturation.id_unite 哪里 structures_proposer_prestations.id_presta_struct_unit = '1';

我不知道该怎么办我没有找到有同样问题的帖子

谁能指出我正确的方向？

提前谢谢你。

编辑:

协会

models.structures_employer_ressources.hasMany(models.ressources, { foreignKey: 'id_ressource' });
models.ressources.belongsTo(models.structures_employer_ressources, { foreignKey: 'id_ressource' });

资源模型

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('ressources', {
    id_ressource: {
      type: DataTypes.STRING,
      allowNull: false,
      primaryKey: true
    }
 ........
  },{
    tableName: 'ressources',
    updatedAt: 'date_modification',
    createdAt: 'date_creation'
  });
};

和structures_employer_ressources的模型

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('structures_employer_ressources', {
    id_structure: {
      type: DataTypes.STRING,
      allowNull: false,
      primaryKey: true,
      references: {
        model :'structures',
        key: 'id_structure'
      }
    },
    id_ressource: {
      type: DataTypes.STRING,
      allowNull: false,
      primaryKey: true,
      references: {
        model :'ressources',
        key: 'id_ressource'
      }
    }
  },{
    tableName: 'structures_employer_ressources',
    updatedAt: 'date_modification',
    createdAt: 'date_creation'
  });
};

Answer 1

如果您将一个数组提供给初始连接的 where 子句，您可以对连接运行原始查询。

例子:

 models.structures.findAll({
where:[["[structures_proposer_prestations].[id_presta_struct_unit] = " + req.params.id_presta_struct_unit, null]],
    include: {
      required: false,
      model: models.structures_proposer_prestations,
      where: {
        id_presta_struct_unit: req.params.id_presta_struct_unit
      },
      include: {
        model : models.unites_facturation,
      }
    }
  }

数组也可以采用标准的对象语法，并由 AND 组合。我传入的 null 是针对参数的，因此绝对可以优化它以将 id 作为参数，只是不知道手头的语法。