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

javascript, node.js, sequelize.js

Solution

Answer from the github page - need to use `belongsTo` instead of `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' });
            },
        },
    });
};

Problem

I made a simple test that is to search for an address (id = 4) 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({ 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(''); }); }); ``` 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` 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 ``` One can observe that it is impossible to retrieve the result via "include" and "getUser()". The error is visible in the log of 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); ``` While the correct answer should have been: ``` "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); ``` So my question is, what is the configuration to put in my Model or my request for the result to be correct with "include" and "getUser()" ? Thank you. (Also posted on: https://github.com/sequelize/sequelize/issues/3182)

Original source