mysql - Sequelize 与 mysql 的关系未执行正确的查询

Question

我正在尝试在sequelize中与mysql进行关联，但没有得到预期的结果

这是我的用户模型:

const bcrypt = require('bcrypt');

module.exports = (sequelize, DataTypes) => {
const user = sequelize.define('user', {
    displayName: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
    },
    email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
    },
    active: DataTypes.BOOLEAN,
    description: DataTypes.STRING,
    password: {
    type: DataTypes.STRING,
    allowNull: false
    },
    gender: DataTypes.CHAR(1),
    presentationSentence: DataTypes.STRING,
    hairId:  DataTypes.INTEGER,
    createdAt: DataTypes.DATE,
    updatedAt: DataTypes.DATE,
}, {
    hooks: {
    beforeCreate: user => {
        const salt = bcrypt.genSaltSync();

        user.password = bcrypt.hashSync(user.password, salt);
    }
    },
    isValidPassword: (password, salt) => {
    return bcrypt.compareSync(password, salt);
    }
});

user.associate = models => {
    user.hasOne(models.hair, {
    foreignKey: 'id',
    sourceKey: 'hairId'
    });

    user.hasMany(models.photo, {
    foreignKey: 'userId'
    });
};

return user;
};

我有一个头发模型:

module.exports = (sequelize, DataTypes) => {
const hair = sequelize.define('hair', {
    type: DataTypes.ENUM('Liso', 'Crespo', 'Ondulado', 'Cacheado', 'Calvo')
});

return hair;
};

问题是，当我执行查询时，它会查找带有用户 ID 的 hair 模型，而不是 hairId 字段；

SELECT `user`.`id`, `user`.`displayName`, `user`.`email`, `user`.`active`, `user`.`description`, `user`.`password`, `user`.`gender`, `user`.`presentationSentence`, `user`.`hairId`, `user`.`createdAt`, `user`.`updatedAt`, `hair`.`id` AS `hair.id`, `hair`.`type` AS `hair.type`, `hair`.`createdAt` AS `hair.createdAt`, `hair`.`updatedAt` AS `hair.updatedAt`, `photos`.`id` AS `photos.id`, `photos`.`url` AS `photos.url`, `photos`.`active` AS `photos.active`, `photos`.`avatar` AS `photos.avatar`, `photos`.`userId` AS `photos.userId`, `photos`.`createdAt` AS `photos.createdAt`, `photos`.`updatedAt` AS `photos.updatedAt` FROM `users` AS `user` LEFT OUTER JOIN `hairs` AS `hair` ON `user`.`id` = `hair`.`id` LEFT OUTER JOIN `photos` AS `photos` ON `user`.`id` = `photos`.`userId`;

所以，它总是给我带来 id 1 的头发字段

有谁知道为什么会这样吗？

我花了很多时间来解决这个问题，但无法找到解决方案

Answer 1

你的头发模型定义错误，给它一个自增主键和一个枚举值。

module.exports = (sequelize, DataTypes) => {
const hair = sequelize.define('hair', {
    hair_id: {
      type: DataTypes.BIGINT.UNSIGNED,
      autoIncrement: true,
      primaryKey: true,
    },
    email: {
      type: DataTypes.STRING,
      references: {
        model: 'Users',
        key: 'email',
      }
    }
    hair_type: {
      type: DataTypes.ENUM,
      values: [
        'Liso',
        'Crespo',
        'Ondulado',
        'Cacheado',
        'Calvo'
      ],
      defaultValue: 'Liso',
      notNull: true
    }
});

return hair;
};

在协会中，

user.associate = models => {
    user.hasOne(models.hair, {
    foreignKey: 'email',
    sourceKey: 'email'
    });

    user.hasMany(models.photo, {
    foreignKey: 'userId'
    });
};