Geek Answers Handbook

Sequelize find by association via manual binding table

I know that a simpler case is described here :

Unfortunately, my case is a bit more complicated. I have a User model that belongsToManyDepartments (which, in turn, belongsToManyUsers), but does this through a userDepartmentmanually defined connection table. My goal is to get all users belonging to a specific department. First, consider 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 look 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 in 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 request:

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

})

The following error failed:

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

Attempting to include the userDepartment model results in:

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

: Sequelize M: M. ( , A "" B). " " .

"^ 2.0.5"

+4
3

, :

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

, Sequelize " ", where model_name. , , where user.departments.id, department departments.id. (), , .

+3

, .

sequelize.literal.

:

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

, .

+3

, , , Github.

:

where: {
   '$Table.column$' : value
 }
-1

More articles:


All Articles