CODEWITHSUNDEEP
mysqlsqlnode.jsjoinsequelize.js
Suhail Gupta
Suhail Gupta

Reputation: 23276

Unable to run a join query using sequelize

I need to run a join query using sequelize and I have been reading the documentation at sequelize doc. But as I run the following snippet, I get an error.

    let channelUsersM = UserModel.get(); // Table name: channel_users
    let channelM = ChannelModel.get(); // Table name: channel

    channelUsersM.belongsTo(channelM, {as: 'channel',foreign_key: 'channel_id',targetKey:'id'});
    channelM.hasMany(channelUsersM,{foreign_key: 'channel_id'});

    channelUsersM.findAll({
        attributes: ['username'],
        where: {
            usertype: this.userType,
            channel: {
                name: channelName
            }
        },
        include: [channelM]
    }).then((r) => {
        resolve(r);
    }).catch((err) => {
        reject(err);
    });

Error says: channel is not associated to channel_users!

What could be the reason for this? I know how to directly run a SQL query using sequelize, but I do not want to go with it.

For easier understanding here, is the equivalent sql query that I am trying with sequelize:

select cu.username from channel as ch left join 
channel_users as cu on ch.id = cu.channel_id 
ch.name = 'some-name' and cu.usertype = 'some-type';

Here is the definition of models if required:

For channel_users:

    channel_id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        primaryKey: true,
        field: 'channel_id'
    },
    userid: {
        type: Sequelize.INTEGER,
        field: 'userid'
    },
    username: {
        type: Sequelize.CHAR(255),
        field: 'username'
    },
    password: {
        type: Sequelize.TEXT,
        field: 'password'
    },
    usertype: {
        type: Sequelize.ENUM('user', 'moderator','speaker','owner'),
        field: 'usertype'
    }

For channel:

    id: {
        type: Sequelize.INTEGER,
        field: 'id',
        autoIncrement: true,
        primaryKey: true
    },
    name: {
        type: Sequelize.CHAR(255),
        field: 'name'
    },
    display_name: {
        type: Sequelize.TEXT,
        field: 'display_name'
    },
    creatorid: {
        type: Sequelize.INTEGER,
        field: 'creatorid'
    },
    password: {
        type: Sequelize.TEXT,
        field: 'password'
    },
    createdAt: {
        type: Sequelize.DATE,
        field: 'createdAt'
    },
    modifiedAt: {
        type: Sequelize.DATE,
        field: 'modifiedAt'
    }

Upvotes: 0

Views: 230

Answers (1)

piotrbienias
piotrbienias

Reputation: 7411

You have defined an alias in the belongsTo association, so you also need to include the alias in include attribute when querying. Moreover, the channel.name column value should also be included in the include object of the query.

channelUsersM.findAll({
    attributes: ['username'],
    where: {
        usertype: this.userType
    },
    include: [
        {
            model: channelM,
            as: 'channel',
            attributes: [],
            where: { name: channelName }
        }
    ]
}).then((r) => {
    resolve(r);
}).catch((err) => {
    reject(err);
});

The attributes: [] in include is added in order to prevent returning any fields from the channel table (according to you query you want only the username field from channel_users table).

Upvotes: 1

Related Questions