我在两个模型,用户和组之间 Build 了多对多的关系 .
我有两个模型,它们使用foreignKey和through属性指定belongsToMany .
用户模型
'use strict';
module.exports = function(sequelize, DataTypes) {
var User = sequelize.define('User', {
firstName: DataTypes.STRING,
lastName: DataTypes.STRING,
email: DataTypes.STRING,
username: DataTypes.STRING,
facebookId: DataTypes.STRING,
password: DataTypes.STRING,
}, {
classMethods: {
associate: function(models) {
User.hasMany(models.Payment);
User.hasMany(models.Friend, {foreignKey: 'userIdLink1'});
User.belongsToMany(models.Group, { through: 'UsersGroups', foreignKey: 'facebookId' });
}
},
instanceMethods: {
toJSON: function () {
var values = Object.assign({}, this.get());
delete values.password;
return values;
}
}
});
return User;
};
团体模型
'use strict';
module.exports = function(sequelize, DataTypes) {
var Group = sequelize.define('Group', {
name: DataTypes.STRING
}, {
classMethods: {
associate: function(models) {
// associations can be defined here
Group.belongsToMany(models.User, {through: 'UsersGroups', foreignKey: 'groupId'});
Group.hasMany(models.Payment)
}
},
instanceMethods: {
toJSON: function () {
var values = Object.assign({}, this.get());
delete values.password;
return values;
}
}
});
return Group;
};
通过联结表加入 UsersGroups
这工作正常,我可以创建一个新的组,它成功地链接用户,但当我尝试获取数据时,SQL查询试图找到基于User.id的组,而不是像我在模型中指定的User.facebookId User.belongsToMany(models.Group, { through: 'UsersGroups', foreignKey: 'facebookId' });
我调用以下代码来获取数据:
const options = {
where: {
facebookId: facebookId,
},
defaults: {
firstName: data.firstName,
lastName: data.lastName,
email: data.email,
facebookId: facebookId
},
include: [
{ model: db.Group }
]
}
db.User.findOrCreate(options)
.then((user) => {
res.send(user)
}, (err) => {
res.status(500).send(err)
})
它返回一个用户,但是有一个空的Groups数组是不正确的,因为肯定有数据,因为我可以创建它并在DB中查看它 .
您可以在此处查看Sequelize生成的SQL查询:
SELECT `User`.*, `Groups`.`id` AS `Groups.id`, `Groups`.`name` AS `Groups.name`, `Groups`.`createdAt` AS `Groups.createdAt`, `Groups`.`updatedAt` AS `Groups.updatedAt`, `Groups.UsersGroups`.`createdAt` AS `Groups.UsersGroups.createdAt`, `Groups.UsersGroups`.`updatedAt` AS `Groups.UsersGroups.updatedAt`, `Groups.UsersGroups`.`facebookId` AS `Groups.UsersGroups.facebookId`, `Groups.UsersGroups`.`groupId` AS `Groups.UsersGroups.groupId`
FROM (
SELECT `User`.`id`, `User`.`firstName`, `User`.`lastName`, `User`.`email`, `User`.`username`, `User`.`facebookId`, `User`.`password`, `User`.`createdAt`, `User`.`updatedAt` FROM `Users` AS `User` WHERE `User`.`facebookId` = '1341052992643877' LIMIT 1) AS `User`
LEFT OUTER JOIN (`UsersGroups` AS `Groups.UsersGroups`
INNER JOIN `Groups` AS `Groups` ON `Groups`.`id` = `Groups.UsersGroups`.`groupId`
)
ON `User`.`id` = `Groups.UsersGroups`.`facebookId`;
注意最后一行
ON `User`.`id` = `Groups.UsersGroups`.`facebookId`
需要是
ON `User`.`facebookId` = `Groups.UsersGroups`.`facebookId`
1 回答
事实证明我必须在我的用户模型中指定
facebookId
作为主键