CODEWITHSUNDEEP
javascriptsqlnode.jssequelize.js
Adam
Adam

Reputation: 41

Sequelize WHERE NOT EXISTS()

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.

Upvotes: 4

Views: 9156

Answers (2)

Saul Euan
Saul Euan

Reputation: 31

Use rawQuery

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

Upvotes: 0

Naor Zakk
Naor Zakk

Reputation: 86

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.com/sequelize/sequelize/issues/4099

Upvotes: 4

Related Questions