sequelize.js - 使用外键时，获取 "User.DepartmentDepartmentId"而不是 "User.DepartmentId"

Question

在尝试重新创建数据库结构时使用 Sequelize cli/js 时，我在使用外键连接表时出现奇怪的行为。它似乎在外键中添加了模型名称，即使我专门分配了一个。方言是 MSSQL

用户模型

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    UserId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    FirstName: {
      type: DataTypes.STRING,
      allowNull: false
    },
    LastName: {
      type: DataTypes.STRING,
      allowNull: false
    },
    Username: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true
    },
    Email: {
      type: DataTypes.STRING,
      allowNull: false
    },
    Password: {
      type: DataTypes.STRING,
      allowNull: false
    },
    OfficePhone: {
      type: DataTypes.STRING
    },
    DepartmentId: {
      type: DataTypes.INTEGER
    },
    ContractorId: {
      type: DataTypes.INTEGER
    },
    IsActive: {
      type: DataTypes.INTEGER,
      allowNull: false,
      defaultValue: 1
    },
    CreatedAt: {
      allowNull: false,
      type: DataTypes.DATE
    },
    UpdatedAt: {
      allowNull: false,
      type: DataTypes.DATE
    },
    DeletedAt: {
      type: DataTypes.DATE
    }
  }, {
    paranoid: true,
    timestamps: true,
    createdAt: 'CreatedAt',
    updatedAt: 'UpdatedAt',
    deletedAt: 'DeletedAt',
    tableName: 'AppUsers',
    indexes: [
      {
        unique: true,
        fields: ['Username']
      }
    ]
  });
  User.associate = function(models) {
    User.belongsTo(models.Department, {
      targetKey: 'DepartmentId'
    });
    User.belongsTo(models.Contractor, {
      targetKey: 'ContractorId'
    });
  };
  return User;
};

部门型号

  const Department = sequelize.define('Department', {
    DepartmentId: {
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
      type: DataTypes.INTEGER
    },
    DepartmentName: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true
    },
    CreatedAt: {
      allowNull: false,
      type: DataTypes.DATE
    },
    UpdatedAt: {
      allowNull: false,
      type: DataTypes.DATE
    },
    DeletedAt: {
      type: DataTypes.DATE
    }
  }, {
    paranoid: true,
    timestamps: true,
    createdAt: 'CreatedAt',
    updatedAt: 'UpdatedAt',
    deletedAt: 'DeletedAt',
    tableName: 'AppDepartments',
    indexes: [
      {
        unique: true,
        fields: ['DepartmentName']
      }
    ]
  });
  Department.associate = function(models) {
    Department.hasMany(models.User, {
      foreignKey: 'DepartmentId'
    });
  };
  return Department;
};

查找所有

exports.getUsers = async (req, res, next) => {
  const showDeleted = ( req.headers.showdeleted == 'false' ); 
  await User.findAll({
    attributes: ['UserId', 'FirstName', 'LastName', 'Username', 'Email', 'OfficePhone', 'IsActive', 'DeletedAt'],
    include: [
      { model: Department, attributes: ['DepartmentName'] },
      { model: Contractor, attributes: ['ContractorName'] }
    ],
    paranoid: showDeleted,
    order: [
      ['LastName', 'ASC']
    ]
  })
    .then(results => {
      res.status(200).send(results);
    })
    .catch(err => {
      console.log(err);
    })
};

生成的查询

SELECT [User].[UserId], 
[User].[FirstName], 
[User].[LastName], 
[User].[Username], 
[User].[Email], 
[User].[OfficePhone], 
[User].[IsActive], 
[User].[DeletedAt], 
[Department].[DepartmentId] AS [Department.DepartmentId], 
[Department].[DepartmentName] AS [Department.DepartmentName], 
[Contractor].[ContractorId] AS [Contractor.ContractorId], 
[Contractor].[ContractorName] AS [Contractor.ContractorName] 
FROM [DrxAppUsers] AS [User] 

LEFT OUTER JOIN [DrxAppDepartments] AS [Department] ON [User].[DepartmentDepartmentId] = [Department].[DepartmentId] AND ([Department].[DeletedAt] IS NULL) 

LEFT OUTER JOIN [DrxAppContractors] AS [Contractor] ON [User].[ContractorContractorId] = [Contractor].[ContractorId] AND ([Contractor].[DeletedAt] IS NULL) WHERE ([User].[DeletedAt] IS NULL) ORDER BY [User].[LastName] ASC;

正如你在上面看到的，它试图加入“User.DepartmentDepartmentId”(还有 User.ContractorContractorId 因为 ContractorId 也是一个外键)。为什么会这样，我该如何解决？我已经尝试了许多不同的选项，以及外键没有的选项，但似乎无法弄清楚。

Answer 1

创建关联时，您需要指定 外键 的命名。如果不指定，默认为目标名称+目标主键。所以对你来说这是 DepartmentDepartmentId 并且你想要 DepartmentId

User.belongsTo(models.Department, {
  targetKey: 'DepartmentId',
  foreignKey: 'DepartmentId' // the key in your source table
});

Ref