CODEWITHSUNDEEP
mysqlnode.jssequelize.js
user1657861
user1657861

Reputation: 281

Sequelize help in associations

All, Attached below is a SQL query that I used as a raw query in sequelize and it worked fine. Now I am trying to setup it up with mixin associations in sequelize but unable to do so. Did go thru the help documentations but could not completely grasp the details. Maybe its just me. Anyway, attached below is the query that I am trying to rework. Would appreciate if anybody can throw some pointers or have some samples to help me rework this? Thanks in advance.

select cust.*, cust_role.id, cust_role.rolename, role.roletype
from
customer cust, customer_roles cust_role, roles role
where
cust.id = cust_role.user and
cust_role.role = role.id and
cust.id like '0123456' //customerId

table relationships

customer_roles has a foreign key to both roles and customer tables
customer to customer_roles (1 to many), 
customer_roles to roles (many to 1)

I am importing the models

var custModel = sequelize.import('../models/customer.js');
var custRolesModel = sequelize.import('../models/customer_roles.js');
var roleModel = sequelize.import('../models/roles.js');

trying to set the relationships

custModel.hasMany(custRolesModel);
custRolesModel.belongsTo(custModel);

roleModel.hasMany(custRolesModel);
custRolesModel.belongsTo(roleModel);

and finally trying to get the customer..

custModel.findAll({
  include:[{
    model:custRolesModel,
    include:[{
      model:roleModel,
      where: {
        username:req.body.username
      },
      required:false
    }]
  }]
}).then(function (result) {
......

Upvotes: 1

Views: 208

Answers (2)

user1657861
user1657861

Reputation: 281

Based on the suggestion from above and another suggestion one in SO, I was able to resolve it. This is what I did incase anybody needs to know.

https://stackoverflow.com/a/36006808/1657861

Modified relationships

custModel.belongsToMany(custRolesModel, {
  through:'customer_roles', //Used table name
  foreignKey: 'user',
  otherKey:'id'
});

custRolesModel.belongsToMany(roleModel, {
  through: 'customer_roles', //Used table name
  foreignKey: 'role',
  otherKey: 'id'
});

usersModel.findAll({
  where : {
    username:req.body.username
  },
  include:[{
    model:userRolesMembershipModel,
    include:[{
      model:roleModel
    }]
  }]
})

Upvotes: 1

Steffen Langer
Steffen Langer

Reputation: 1193

This might work:

custModel.findAll({
  where : {
    id:req.body.userId
  },
  include:[{
    model:custRolesModel,
    include:[{
      model:roleModel
    }]
  }]
}).then(function (result) {
......

Note that the where clause belongs to the customer model instead of the role model.

Upvotes: 1

Related Questions