filter Sequelize belongsToMany get association table

Question

I am working with Sequelize 4.37.10 and it works great. Unfortunately the documentation is not perfect in my opinion. So it lacks a bit of describing the belongsToMany possibilities.

I have the following problem:

I defined my tables like this:

const Department = db.define('department', {
  name: {type: Sequelize.STRING, allowNull: false},
  shortName: {type: Sequelize.STRING, allowNull: false}
})

const Employee = db.define('employee', {
  title: {type: Sequelize.STRING},
  name: {type: Sequelize.STRING, allowNull: false},
  surname: {type: Sequelize.STRING, allowNull: false},
  .
  .
  role: {type: Sequelize.STRING}
})

Then I associated the tables like this:

const EmployeeDepartments = db.define('employeeDepartments', {
  manager: {type: Sequelize.BOOLEAN, allowNull: false}
})

Department.belongsToMany(Employee, {through: EmployeeDepartments})
Employee.belongsToMany(Department, {through: EmployeeDepartments})

Now i want to get all department employees with the manager field set to true. The creation was no problem, but the select is a problem for me.

I tried the following with no luck:

department.getEmployees({where: {manager: true}})

I also thought of scopes but I don't know how to design that properly.

Can you help me with that?

score 4 · Accepted Answer · answered Jun 19 '18 at 12:06

4

Funnily, I was just looking for the exact same thing, and the only result was your question from a few hours ago...

I solved it by now, what you need is the following:

department.getEmployees({ through: { where: { manager: true } } })

You can also get all employees of a department of which you only have the id:

const department = Department.build( { id: departmentId } );
// proceed as above

answered Jun 19 '18 at 12:06

Danyel

2,180
18
32

Nice! Sad to know that the [Sequelize docs](http://docs.sequelizejs.com/class/lib/associations/belongs-to-many.js~BelongsToMany.html#instance-method-get) does not tell anything about the `through` option. Thank you! In my opinion, the second code line is easier by using `Department.findById(departmentId)` – Patcher56 Jun 19 '18 at 12:18
1

The problem with that approach is that you hit the DB. The `build` doesn't. – Danyel Jun 19 '18 at 12:47
Also, you may run into this problem that you cannot really remove the association field `departmentEmployee` from the resulting employees (see this issue: https://github.com/sequelize/sequelize/issues/3664). I solved this with `employees.forEach( e => delete e.dataValues['department_employee'] )` – Danyel Jun 19 '18 at 14:16

Priyank · Answer 2 · 2018-06-19T10:17:57.237

2

department.getEmployees({where: {manager: true}})

change this code with this.

department.getEmployees(      
  include:{
    model: employeedepartments, // your employee departments model name
    {where: {manager: true},      
    required:true, 
  }
})

You have to just add include in your query

edited Jun 19 '18 at 10:17

answered Jun 19 '18 at 10:02

Priyank

470
2
11

include expects an array – Alexey Sh. Dec 09 '19 at 03:05

score 0 · Answer 3 · answered Jun 19 '18 at 12:14

I found a very simple answer myself after searching a while.

When using belongsToMany Sequelize does use a join to get data from the second table (I knew that).

I had the option to use the following syntax:

department.getEmployees({where: {'$employeeDepartments.manager$': true}})

I found that from a page on google but unfortunately I lost the url. Maybe this helps someone with a similar problem.

filter Sequelize belongsToMany get association table

3 Answers3