Load attributes from associated model in sequelize.js

Question

I have two models. User and Manager

User Model

 const UserMaster = sequelize.define('User', {
        UserId: {
            type: DataTypes.BIGINT,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        RelationshipId: {
            type: DataTypes.STRING,
            allowNull: true,
            foreignKey: true
        },
        UserName: {
            type: DataTypes.STRING,
            allowNull: true
        }
    })

Manager model

 const Manager = sequelize.define('Manager', {
        ManagerId: {
            type: DataTypes.BIGINT,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        RelationshipId: {
            type: DataTypes.STRING,
            allowNull: true,
            foreignKey: true
        },
        MangerName: {
            type: DataTypes.STRING,
            allowNull: true
        }
    })

Models are minified to simplyfy the problem

Associations..

User.belongsTo(models.Manager, {
    foreignKey: 'RelationshipId',
    as: 'RM'
});

Manger.hasMany(model.User, {
    foreignKey: 'RelationshipId',
    as: "Users"
})

So, on user.findAll()

var userObject = models.User.findAll({
    include: [{
        model: models.Manager,
        required: false,
        as: 'RM',
        attributes: ['ManagerName']
    }]
});

I get the following.

userObject = [{
        UserId: 1,
        RelationshipId: 4545,
        UserName: 'Jon',
        RM: {
            ManagerName: 'Sam'
        }
    },
    {
        UserId: 2,
        RelationshipId: 432,
        UserName: 'Jack',
        RM: {
            ManagerName: 'Phil'
        }
    },
    ...
]

How can I move 'ManagerName' attribute from Manager model (associated as RM) to UserObject? Is it possible to somehow load attributes from eagerly-loaded models without nesting them under a separate object? I expected the resulting Object to look Like the object

Expected Object --

userObject = [{
        UserId: 1,
        RelationshipId: 4545,
        UserName: 'Jon',
        ManagerName: 'Sam' // <-- from Manager model
    },
    {
        UserId: 2,
        RelationshipId: 432,
        UserName: 'Jack',
        ManagerName: 'Phil' // <-- from Manager model
    },
    ...
]

Thank you.

But that will return me RM.ManagerName. How do I rename (or manupulate) this to just ManagerName ? — Kshateesh, Apr 25 '18 at 04:37
Further adding ` attributes: { include: [Sequelize.col('RM.ManagerName'), 'ManagerName'] }` worked. Thank you @Ellebkey — Kshateesh, Apr 25 '18 at 06:49

score 9 · Accepted Answer · answered Apr 25 '18 at 06:55

9

Adding raw: true along with attributes option worked to get the desired object format.

So,

var userObject = models.User.findAll({
raw:true,
attributes: {
include: [Sequelize.col('RM.ManagerName'), 'ManagerName']
},
    include: [{
        model: models.Manager,
        required: false,
        as: 'RM',
        attributes: []
    }]
});

answered Apr 25 '18 at 06:55

Kshateesh

571
1
8
21

Thanks this saved me a lot of time. The key solution is to use 'Sequelize.col' to replace an associated model's attribute name. – newguy May 14 '20 at 15:28
With TS get the error: Type 'Col' is not assignable to type 'string | ProjectionAlias'. Type 'Col' is not assignable to type 'ProjectionAlias' – coler-j Jun 10 '20 at 17:04

Load attributes from associated model in sequelize.js

1 Answers1

Linked