Wednesday, 15 March 2017

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'
    }



via Suhail Gupta

No comments:

Post a Comment