vendredi 20 février 2015

On sequelize, "include" of "findOne" not working

I made a simple test that is to search for an address (no matter where it is) and retrieve the user who is linked to that address.


Here are my Models:


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' });
},
},
});
};


And here is my test file:



db.UserAddress.findOne({
include: [
{ model: db.User },
],
}).then(function(address) {
console.log('------------------------------ User by "include"');
console.log('Address title: '+address.title);
if(address.user !== undefined) {
console.log('User name: '+address.user.name);
} else {
console.log('User name: NO USER');
}
console.log('');
address.getUser().then(function(user) {
console.log('------------------------------ User by "getUser"');
console.log('Address title: '+address.title);
console.log('User name: '+user.name);
console.log('');
});
});


I do a query with two tests:



  • The first aims to recover the user directly via the variable "user", so thanks to "include" of the request.

  • And the other also retrieve the user but this time via "getUser()".


Here is the result:



$ 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` LIMIT 1;
------------------------------ User by "include"
Address title: Home
User name: NO USER

Executing (default): SELECT `id`, `name` FROM `user` AS `User` WHERE (`User`.`id`=1);
------------------------------ User by "getUser"
Address title: Home
User name: Valentin


One can observe that it is impossible to retrieve the result via "include". The error is visible in the log of SQL:



LEFT OUTER JOIN `user` AS `User` ON `UserAddress`.`id` = `User`.`id`


While the correct answer should have been:



LEFT OUTER JOIN `user` AS `User` ON `UserAddress`.`user_id` = `User`.`id`


So my question is, what is the configuration to put in my Model or my request for the result to be correct with "include" ?


Thank you.


Aucun commentaire:

Enregistrer un commentaire