user1657861 user1657861 - 5 months ago 18
MySQL Question

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) {
......

Answer

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.