CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Jonathan Solorzano
Jonathan Solorzano

Reputation: 7022

Get the data from related tables (models) sequelize

Let's say I have these tables:

countries
    id - integer
    name - string

users
    id - integer
    countryId - integer
    name - string

What I want is to get all the users with their country names instead of the country ids... Sequelize docs are good, but confusing...

I got the following func to get all the users:

function get( request, response ) {

    models.users.findAll( {

        order: [ [ request.query.orderBy, request.query.sort ] ]

    } ).then( function ( users ) {

        response.json( users );

    } );

}

And here's my users.js model:

'use strict';
module.exports = function ( sequelize, DataTypes ) {

    var users = sequelize.define( 'users', {
        name: DataTypes.STRING( 50 ),
        countryId: DataTypes.INTEGER,
    }, {
        classMethods: {
            associate: function ( models ) {
                // associations can be defined here
            }
        }
    } );

    return users;

};

So... what do I need to do in order to get the country name instead of the id when querying users model?

Upvotes: 3

Views: 7827

Answers (2)

syldor
syldor

Reputation: 1176

You have to first define the association in your models

'use strict';
module.exports = function ( sequelize, DataTypes ) {

var users = sequelize.define( 'users', {
    name: DataTypes.STRING( 50 ),
    countryId: DataTypes.INTEGER,
}, {
    classMethods: {
        associate: function ( models ) {
            users.belongsTo(models.countries, {foreignKey: countryId})
        }
    }
} );

return users;

};

You can then query using include:

models.users.findAll( {

    order: [ [ request.query.orderBy, request.query.sort ] ],
    include: [{
        model: models.countries
    }]
} ).then( function ( users ) {

    // do some formating on the output

} );

After you can format your output if you only want the country_name.

Upvotes: 4

Jan Aagaard Meier
Jan Aagaard Meier

Reputation: 28778

User.belongsTo(Country)
User.findAll({ include: [Country] })

Each user will have a .country property, where you can get the name - it's not possible to have the country name added directly to the same object, for that you'll have to use a raw query, or format the result afterwards

Upvotes: 1

Related Questions