CODEWITHSUNDEEP
node.jssequelize.js
David
David

Reputation: 660

Sequelize inner join with conditions

I have tried to find a solution for my case but couldn't. I have 3 tables:

Role = sequelize.define('Role', {
  name: {
    type: Sequelize.STRING
  },
  permissions: {
    type: Sequelize.JSONB
  }
});

EmployeeRole = sequelize.define('EmployeeRole', {
  employeeId: {
    type: Sequelize.INTEGER,
    primaryKey: true,
  },
  roleId: {
    type: Sequelize.INTEGER,
    primaryKey: true,
  }
});

And I get employees from other source outside of sequalize.

I have created a adummy object for employees table in the model file as follows:

const Employee = sequelize.define('Employee', {});

with the following associations

 EmployeeRole.belongsTo(Role, { foreignKey: 'roleId', sourceKey: 'id' });
 EmployeeRole.belongsTo(Employee, { foreignKey: 'employeeId' });

I using the employee ID, how can I retrieve the employee's Roles?

Update

The target query I'm trying to achieve is the following where I supply the employee ID:

select * from "Roles" as r,"EmployeeRoles" as er
where r.id = er."roleId"
and er."employeeId" = 2035

Upvotes: 1

Views: 1005

Answers (3)

David
David

Reputation: 660

Thank you guys. The following seems to return the correct values

EmployeeRole.findAll({where:{employeeId: empId},
    include: [
      {model: Role,attributes:['id','name','permissions'],required: true}
    ],
    attributes:['roleId']
  })

Upvotes: 0

Praveenkumar Kalidass
Praveenkumar Kalidass

Reputation: 429

From your description, i assume each Employee can have many Role and each Role belongs to many Employee. As a result this has many to many associations.

Your association will resemble as,

Employee.belongsToMany(models.Role, {
  through: model.EmployeeRole,
  foreignKey: 'employeeId'
});

Role.belongsToMany(models.Employee, {
  through: model.EmployeeRole,
  foreignKey: 'roleId'
});

You can retreive employee roles as,

models.Employee.findAll({
  include: {
    model: models.Role,
    through: {
      attributes: ['id']
    }
  }
})

Hope this helps...

Upvotes: 2

margherita pizza
margherita pizza

Reputation: 7145

Hope you need only an inner join (You don't have any condition ) Try something like this

Employee.findOne({
    where:{
        id:2330
    },
    include:[
    {model:EmployeeRole,}
    include:[
    {model:Role,attributes:['name']}
    ]   
    ]
})

Upvotes: 1

Related Questions