Sequelize, Issue with filtering on the join model for belongsToMany associations

Viewed 519

The Sequelize docs suggest that you can filter your query on join table attributes using the following params on the options object: [options.include[].through.where]

I've tried to use this formulation in the code below and found that the filtering does not work.

Model User and model Network are associated through the join table network-affiliations, which has additional attribute (boolean) 'confirmed'. I can't seem to write a query that returns only confirmed networks associated with a user.

My code is excerpted below.

const network_affiliations = db.define('network_affiliations', {
    networkEmail: { type: Sequelize.STRING },
    confirmed: { type: Sequelize.BOOLEAN }
}, {
    freezeTableName: true,
    defaultScope: {
        where: {
            confirmed: true
        }
    },
});

// User belongs to many Networks and Networks belong to many Users (join table)
User.belongsToMany(Network, { through: network_affiliations });
Network.belongsToMany(User, { through: network_affiliations });

//querying to find one user, including their confirmed networks
User.findOne({
            where: {
                email: req.body.email
            },
            include: [{ model: Network, through: { network_affiliations: { where: { confirmed: true } } } }]
        })

I expect this query to return a user instance with its associated networks -- but only networks where confirmed: true. Instead I'm getting all networks associated with the user (including confirmed: false and confirmed: undefined).

As you can also see in the above code, I tried setting a defaultScope for the join table ({confirmed: true}). This also appears not to do anything.

I've also tried a User.findAll query that is otherwise identical, and that also does not work.

What am I missing, or Sequelize just not working here?

Sequelize version: "^3.30.4"

0 Answers
Related