Sequelize find by association through manually-defined join table

Question

I know that there is a simpler case described here:

Unfortunately, my case is a bit more complex than that. I have a User model which belongsToMany Departments (which in turn belongsToMany Users), but does so through userDepartment, a manually defined join table. My goal is to get all the users belonging to a given department. First let's look at models/user.js:

var user = sequelize.define("user", {
    id: {
        type: DataTypes.INTEGER,
        field: 'emplId',
        primaryKey: true,
        autoIncrement: false
    },
    firstname: {
        type: DataTypes.STRING,
        field: 'firstname_preferred',
        defaultValue: '',
        allowNull: false
    }
    ...
    ...
    ...

    associate: function(models) {
            user.belongsToMany(models.department, {
                foreignKey: "emplId",
                through: 'userDepartment'
                });
            })
    }
    ...
    return user;

Now, a look at models/department.js:

var department = sequelize.define("department", {
    id: {
        type: DataTypes.INTEGER,
        field: 'departmentId',
        primaryKey: true,
        autoIncrement: true
    },
    ...
    classMethods: {
        associate: function(models) {

            department.belongsToMany(models.user, {
                foreignKey: "departmentId",
                through: 'userDepartment',
                onDelete: 'cascade'
            });
        }

    ...
return department;

And finally at models/userDepartment.js:

var userDepartment = sequelize.define("userDepartment", {
    title: {
        type: DataTypes.STRING,
        field: 'title',
        allowNull: false,
        defaultValue: ''
    }
}, {
    tableName: 'user_departments'
});

return userDepartment;

So far so good. However, this query:

models.user.findAll({
    where: {'departments.id': req.params.id},
    include: [{model: models.department, as: models.department.tableName}]

})

Fails with the following error:

SequelizeDatabaseError: ER_BAD_FIELD_ERROR: Unknown column 'user.departments.id' in 'where clause'

Attempting to include userDepartment model results in:

Error: userDepartment (user_departments) is not associated to user!

In short: I have two Sequelize Models with a M:M relationship. They are associated through a manually defined join table (which adds a job title to each unique relationship, i.e., User A is a "Manager" in Department B). Attempting to find Users by Department fails with a bad table name error.

sequelize version "^2.0.5"

Answer 1

Took a couple of hours, but I found my solution:

models.department.find({
    where: {id:req.params.id},
    include: [models.user]

The problem is that Sequelize won't let you "go out of scope" because it begins each where clause with model_name. So, for example, the where clause was trying to compare user.departments.id when the departments table is only joined as departments.id. Since we're querying on a value of the department (the ID), it makes the most since to query for a single department and return their associated users.

Answer 2

I had a similair problem, but in my case I couldn't switch the tables.

I had to make use of the: sequelize.literal function.

In your case it would look like the following:

models.user.findAll({
    where: sequelize.literal("departments.id = " + req.params.id),
    include: [{model: models.department, as: models.department.tableName}]
})

I'm not fond of it, but it works.