How to find all that don't belong to a specific id in belongsToMany?

Question

I have the following models:

const users = sequelize.define('users', { /* definition */ }
const messageGroups = sequelize.define('message_groups', { /* definition */ }

They are associated like this:

models.messageGroups.belongsToMany(models.users, {through: 'user_message_groups'})
models.users.belongsToMany(models.messageGroups, {through: 'user_message_groups'})

How can I return a list of users that are not in a messageGroup with a specific id?

For example, let's say my tables look like this

users

id	name
1	Foo
2	Bar
3	Baz

message_groups

id	name
1	Admin
2	Users

user_message_groups

userId	messageGroupId
1	1
1	2
2	2

Given a message_groups id of 1 (Admin), how can I construct a query that returns the two users that are not in that message_group? (Should return users Bar and Baz)

Answer 1

If you are passing id as a variable, one option is to use just users and user_message_groups table.

First, you need a model definition for user_message_groups.

const userMessageGroups = sequelize.define('user_message_groups', {
    userId: {
        type: DataTypes.INTEGER,
        allowNull: false
    },
    messageGroupId: {
        type: DataTypes.INTEGER,
        allowNull: false
    }
});

Next, create an association between users and user_message_groups table.

models.users.hasMany(models.userMessageGroups);

Then query with exclusive LEFT JOIN.

models.users.findAll({
    include: {
        model: models.userMessageGroups,
        required: false,
        where: {
            messageGroupId: 1
        }
    },
    where: models.sequelize.literal('`user_message_groups.messageGroupId` IS NULL')
});