Sequelize关联查询报错，如何修改数据字段与关联配置？

获取categories表数据时触发关联查询错误，错误日志显示生成的SQL中关联条件为categories.id = transactions.categoryId，但实际transaction表的外键字段是categoriesId。相关错误信息及代码如下：

错误日志 #

SELECT `categories`.`id`, 
`categories`.`name`, 
`categories`.`color`, 
`transactions`.`id` AS `transactions.id`, 
`transactions`.`name_transaction` AS `transactions.name_transaction`,
`transactions`.`categoriesId` AS `transactions.categoriesId`, 
`transactions`.`amount` AS `transactions.amount` 
FROM `categories` AS `categories` 
LEFT OUTER JOIN `transactions` AS `transactions` 
ON `categories`.`id` = `transactions.categoryId`;

错误栈 #

Error
    at Query.run (E:\project\budget_app\server\node_modules\sequelize\lib\dialects\mysql\query.js:52:25)
    at E:\project\budget_app\server\node_modules\sequelize\lib\sequelize.js:311:28
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async MySQLQueryInterface.select (E:\project\budget_app\server\node_modules\sequelize\lib\dialects\abstract\query-interface.js:407:12)   
    at async Function.findAll (E:\project\budget_app\server\node_modules\sequelize\lib\model.js:1134:21)
    at async getAll (E:\project\budget_app\server\app\api\categories\controller.js:5:22)

表模型 #

categories 模型 #

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class categories extends Model {
    static associate(models) {
      categories.hasMany(models.transaction)
    }
  }
  categories.init({
    name: DataTypes.STRING,
    color: DataTypes.STRING
  }, {
    sequelize,
    modelName: 'categories',
  });
  return categories;
};

transaction 模型 #

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class transaction extends Model {
    static associate(models) {
      transaction.belongsTo(models.categories)
    }
  }
  transaction.init({
    name_transaction: DataTypes.STRING,
    categoriesId: DataTypes.INTEGER,
    amount: DataTypes.INTEGER
  }, {
    sequelize,
    modelName: 'transaction',
  });
  return transaction;
};

控制器代码 #

const { categories, transaction } = require('../../db/models');
module.exports = {
  getAll : async(req, res, next) => { 
    try {
      const result = await categories.findAll({
        attributes: ['id', 'name', 'color'],
        include:{
          model: transaction,
          attributes: ['id', 'name_transaction', 'categoriesId', 'amount']
        }
      })

      res.status(200).json({
        message:"Success",
        data: result,
      });
    } catch (error) {
      next(error);
    }
  },
  }

问题根源是Sequelize默认按模型单数名推断外键（categoryId），但你的实际外键是复数形式的categoriesId，需在关联配置中手动指定外键字段：

1. 修改 categories 模型的关联 #

在hasMany方法中添加foreignKey参数：

static associate(models) {
  categories.hasMany(models.transaction, { foreignKey: 'categoriesId' })
}

2. 修改 transaction 模型的关联 #

在belongsTo方法中同步添加foreignKey参数，保证双向关联一致：

static associate(models) {
  transaction.belongsTo(models.categories, { foreignKey: 'categoriesId' })
}

修改后，Sequelize生成的SQL关联条件会自动匹配categories.id = transactions.categoriesId，即可解决该错误。

内容的提问来源于stack exchange，提问作者Reza Arrofi