问题描述
我使用的是尚未创建外键的现有数据库,但我可以使用SQL Query联接两个表,但无法在Sequelize中联接它们。
有两种型号: -用户:
module.exports = (sequelize, DataTypes) => {
var User = sequelize.define('User', {
steamid: DataTypes.STRING,
name: DataTypes.STRING,
img: DataTypes.STRING,
tradelink: DataTypes.STRING,
ban_chat: DataTypes.INTEGER,
block_sms: DataTypes.INTEGER,
balance: DataTypes.INTEGER,
ref: DataTypes.STRING,
refcode: DataTypes.STRING,
ip_address: DataTypes.STRING
}, {
timestamps: false
});
User.associate = function(models) {
// associations can be defined here
User.hasMany(models.Order,{
foreignKey: 'steamid',
as: 'orders'
});
};
return User;
};
顺序:
const customDataTypes = require('../../core').SequelizeTimestamp;
module.exports = (sequelize, DataTypes) => {
var Order = sequelize.define('Order', {
steamid: DataTypes.STRING,
item_name: DataTypes.STRING,
price: DataTypes.FLOAT,
type: DataTypes.STRING,
website: DataTypes.STRING,
amount: DataTypes.INTEGER,
status: DataTypes.INTEGER,
img: DataTypes.STRING,
send_attempts: DataTypes.INTEGER,
message: DataTypes.STRING,
date: customDataTypes.TIMESTAMP,
}, {
timestamps: false
});
Order.associate = function(models) {
// associations can be defined here
Order.belongsTo(models.User, {
foreignKey: 'steamid',
as: 'user'
})
};
return Order;
};
在我的API中,我希望获得用户及其订单。 因此,我在控制器中执行以下操作:
user.findOne({
where: { steamid: req.params.steamId },
include: [{
model: order,
as: 'orders',
limit: 50
}],
})
因此,我希望在响应中有用户订单数组,但由于某种原因,我得到的订单数组为空。
Sequelize执行以下两个查询:
SELECT `User`.`id`, `User`.`steamid`, `User`.`name`, `User`.`img`, `User`.`tradelink`, `User`.`ban_chat`, `User`.`block_sms`, `User`.`balance`, `User`.`ref`, `User`.`refcode`, `User`.`ip_address` FROM `Users` AS `User` WHERE `User`.`steamid` = '1234' LIMIT 1;
这已成功找到用户,但第二个查询不正确:
SELECT `id`, `steamid`, `item_name`, `price`, `type`, `website`, `amount`, `status`, `img`, `send_attempts`, `message`, `date` FROM `Orders` AS `Order` WHERE `Order`.`steamid` IN (1) LIMIT 50;
此部分不正确"WHEREOrder
.steamid
IN(1)"
它正在寻找用户ID(主键)的Steamid is=1的订单,但它应该是"1234"的用户steamid
我的关联有什么问题?
推荐答案
如果steamid不是主密钥,则不应使用"Target Key"和"SourceKey"?
https://sequelize.org/master/manual/associations.html
Order.belongsTo(models.User, {
foreignKey: 'steamid',
targetKey: 'steamid',
as: 'user'
})
Order.belongsTo(models.User, {
foreignKey: 'steamid',
sourceKey: 'steamid',
as: 'user'
})