我做了一个简单的测试,就是搜索一个地址(id=4)并检索链接到该地址的用户。
以下是我的型号:
user.js
module.exports = function(sequelize, DataTypes) {
return sequelize.define('User', {
id: {
type: DataTypes.INTEGER(10).UNSIGNED,
allowNull: false,
field: 'id',
//primaryKey: true,
},
name: {
type: DataTypes.STRING,
allowNull: false,
field: 'name',
},
}, {
freezeTableName: true,
tableName: 'user',
createdAt: false,
updatedAt: false,
classMethods: {
associate: function(models) {
models.User.hasMany(models.UserAddress, { foreignKey: 'userId' });
},
},
});
};
user_address.js
module.exports = function(sequelize, DataTypes) {
return sequelize.define('UserAddress', {
id: {
type: DataTypes.INTEGER(10).UNSIGNED,
allowNull: false,
field: 'id',
},
userId: {
type: DataTypes.INTEGER(10).UNSIGNED,
allowNull: false,
field: 'user_id',
},
title: {
type: DataTypes.STRING,
allowNull: true,
field: 'title',
},
address: {
type: DataTypes.STRING,
allowNull: true,
field: 'address',
},
}, {
freezeTableName: true,
tableName: 'user_address',
createdAt: false,
updatedAt: false,
classMethods: {
associate: function(models) {
models.UserAddress.hasOne(models.User, { foreignKey: 'id' });
},
},
});
};
这是我的测试文件:
db.UserAddress.findOne({
where: { id: 4 },
include: [db.User],
}).then(function(address) {
console.log('------------------------------ Address by "include"');
console.log('Address title: '+address.title);
console.log('User id: '+address.userId);
if(address.User !== null) {
console.log('User name: '+address.User.name);
} else {
console.log('User name: NO USER');
}
console.log('');
address.getUser().then(function(user) {
console.log('------------------------------ Address by "getUser"');
console.log('Address title: '+address.title);
console.log('User id: '+address.userId);
if(user !== null) {
console.log('User name: '+address.user.name);
} else {
console.log('User name: NO USER');
}
console.log('');
});
});
我用两个测试进行查询:
- 第一个目的是通过变量"user"直接恢复用户,因此感谢请求的"include"
- 另一个也检索用户,但这次是通过"getUser()"
结果如下:
$ node test.js
Executing (default): SELECT `UserAddress`.`id`, `UserAddress`.`user_id` AS `userId`, `UserAddress`.`title`, `UserAddress`.`address`, `User`.`id` AS `User.id`, `User`.`name` AS `User.name` FROM `user_address` AS `UserAddress` LEFT OUTER JOIN `user` AS `User` ON `UserAddress`.`id` = `User`.`id` WHERE `UserAddress`.`id`=4;
------------------------------ Address by "include"
Address title: Test
User id: 3
User name: NO USER
Executing (default): SELECT `id`, `name` FROM `user` AS `User` WHERE (`User`.`id`=4);
------------------------------ Address by "getUser"
Address title: Test
User id: 3
User name: NO USER
可以观察到,通过"include"one_answers"getUser()"检索结果是不可能的。错误在SQL:的日志中可见
"include": LEFT OUTER JOIN `user` AS `User` ON `UserAddress`.`id` = `User`.`id`
and
"getUser()": SELECT `id`, `name` FROM `user` AS `User` WHERE (`User`.`id`=4);
而正确的答案应该是:
"include": LEFT OUTER JOIN `user` AS `User` ON `UserAddress`.`user_id` = `User`.`id`
and
"getUser()": SELECT `id`, `name` FROM `user` AS `User` WHERE (`User`.`id`=3);
所以我的问题是,在我的模型中放入什么配置,或者我用"include"one_answers"getUser()"请求结果正确?
谢谢。
(也发布在:https://github.com/sequelize/sequelize/issues/3182)
来自github页面的答案-需要使用belongsTo
而不是hasOne
。
user.js
module.exports = function(sequelize, DataTypes) {
return sequelize.define('User', {
id: {
type: DataTypes.INTEGER(10).UNSIGNED,
allowNull: false,
field: 'id',
//primaryKey: true,
},
name: {
type: DataTypes.STRING,
allowNull: false,
field: 'name',
},
}, {
freezeTableName: true,
tableName: 'user',
createdAt: false,
updatedAt: false,
classMethods: {
associate: function(models) {
models.User.hasMany(models.UserAddress, { foreignKey: 'userId' });
},
},
});
};
user_address.js
module.exports = function(sequelize, DataTypes) {
return sequelize.define('UserAddress', {
id: {
type: DataTypes.INTEGER(10).UNSIGNED,
allowNull: false,
field: 'id',
},
userId: {
type: DataTypes.INTEGER(10).UNSIGNED,
allowNull: false,
field: 'user_id',
},
title: {
type: DataTypes.STRING,
allowNull: true,
field: 'title',
},
address: {
type: DataTypes.STRING,
allowNull: true,
field: 'address',
},
}, {
freezeTableName: true,
tableName: 'user_address',
createdAt: false,
updatedAt: false,
classMethods: {
associate: function(models) {
models.UserAddress.belongsTo(models.User, { foreignKey: 'userId' });
},
},
});
};