I have a many to many relations that looks like this:

var Genres = db.define('Movie', {
    name: {
        type: Sequelize.STRING(100),
        allowNull: false
    },
    description: {
        type:Sequelize.STRING(),
        allowNull: true
    },
    thumbnail: {
        type: Sequelize.BLOB(),
        allowNull: true
    },
    urlposter: {
        type: Sequelize.STRING(245),
        allowNull: true
    }
});
var Users = db.define('User', {
    username: {
        type: Sequelize.STRING(25),
        allowNull: false
    },
    password: {
        type: Sequelize.STRING(25),
        allowNull: false
    }
});
Movies.belongsToMany(Users, {through: UM, foreignKey: 'Id_Movies'});
Users.belongsToMany(Movies, {through: UM, foreignKey: 'Id_Users'});

what I will do is return all Movies that have no link to a specific user

this is my SQL query i want to achive:

SELECT m.* 
FROM Movies m 
WHERE NOT EXISTS(SELECT NULL 
FROM Users_Movies sc 
WHERE sc.Id_Movies = m.id AND sc.Id_Users = 1)

This is the closest I've got but this just return all movies that have a link u user with ID 1

            Movies.findAll({
            include: [
            // {
            //  model:Genres,
            //  through:{attributes:[]}
            // },
            {
                model:Users,
                through:{attributes:[]},
                where: {id: 1},
                required: true,
            }],
        })
        .then(function(movies){
            done(null, movies);
        })
        .catch(function(error){
            done(error, null);
        });

But I want to invert it.

right now my only option is to make 2 queries one with all movies and one with all movies that have a link and loop through the list and remove them from the first list.. this is not pretty code.

I have a many to many relations that looks like this:

var Genres = db.define('Movie', {
    name: {
        type: Sequelize.STRING(100),
        allowNull: false
    },
    description: {
        type:Sequelize.STRING(),
        allowNull: true
    },
    thumbnail: {
        type: Sequelize.BLOB(),
        allowNull: true
    },
    urlposter: {
        type: Sequelize.STRING(245),
        allowNull: true
    }
});
var Users = db.define('User', {
    username: {
        type: Sequelize.STRING(25),
        allowNull: false
    },
    password: {
        type: Sequelize.STRING(25),
        allowNull: false
    }
});
Movies.belongsToMany(Users, {through: UM, foreignKey: 'Id_Movies'});
Users.belongsToMany(Movies, {through: UM, foreignKey: 'Id_Users'});

what I will do is return all Movies that have no link to a specific user

this is my SQL query i want to achive:

SELECT m.* 
FROM Movies m 
WHERE NOT EXISTS(SELECT NULL 
FROM Users_Movies sc 
WHERE sc.Id_Movies = m.id AND sc.Id_Users = 1)

This is the closest I've got but this just return all movies that have a link u user with ID 1

            Movies.findAll({
            include: [
            // {
            //  model:Genres,
            //  through:{attributes:[]}
            // },
            {
                model:Users,
                through:{attributes:[]},
                where: {id: 1},
                required: true,
            }],
        })
        .then(function(movies){
            done(null, movies);
        })
        .catch(function(error){
            done(error, null);
        });

But I want to invert it.

right now my only option is to make 2 queries one with all movies and one with all movies that have a link and loop through the list and remove them from the first list.. this is not pretty code.

• javascript
• sql
• node.js
• sequelize.js

Share Improve this question Follow edited Dec 2, 2020 at 6:53 Lin Du 103k136136 gold badges334334 silver badges566566 bronze badges asked Nov 21, 2015 at 14:24 AdamAdam 4111 silver badge33 bronze badges 3

• You say you want to get all movies with no link to a specific user, yet the SQL you say you want to achieve has AND sc.Id_Users = 1. Is this not tied to a particular user? – user1477388 Commented Nov 21, 2015 at 14:29
• No it is not... it you run it, it will return all movies that is not tied to the user with id 1 – Adam Commented Nov 21, 2015 at 15:26
• Remove required: true from the included Users model and add where: {'Users.id': null} to the query. I'm not sure about exact field naming, but I think you've got the idea. – Alexandr Zarubkin Commented Nov 25, 2018 at 22:59

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 4

I know that I'm late on the matter, but I think that using the literal 'users.id IS NULL' in the query's 'where' will do the trick:

 Movie.findAll({
    where: sequelize.literal("users.id IS NULL"),
    include: [
      {
        model: Users,
        through: { attributes: [] }
      }],
  })
    .then(function (movies) {
      done(null, movies);
    })
    .catch(function (error) {
      done(error, null);   
    });

This solutions is based on the following Github issue: https://github./sequelize/sequelize/issues/4099

Use rawQuery

const projects = await sequelize.query('SELECT * FROM projects', {
  model: Projects,
  mapToModel: true // pass true here if you have any mapped fields
});