Exclude User with specific role [Sequelize]

asdc 0
Viewed 11

I currently has

const User = db.define(
  'users',
  {
    id: {
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
      type: Sequelize.INTEGER
    },
    email: {
      type: Sequelize.STRING(255)
    },
   }
const UserRole = db.define(
  'user_roles',
  {
    id: {
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
      type: Sequelize.INTEGER
    }
  },
);
const Role = db.define(
  'organization_roles',
  {
    id: {
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
      type: Sequelize.INTEGER
    },
    roleName: Sequelize.STRING(255),
  },
);

User.belongsToMany(Role, {
  as: 'roles',
  through: UserRole,
  foreignKey: 'userId'
});
Role.belongsToMany(User, {
  as: 'organizationUsers',
  through: UserRole,
  foreignKey: 'roleId'
});

How can I query all the User which doesn't have specific role?
For example:
User1 has role: Teacher, Guardian
User2 has role: Teacher
I want to query user that not have Guardian in their role --> I will get User2.

sequelize.js
1 Answers

Sequelize does not support the NOT EXISTS condition out of the box. So you need to use Sequelize.where and Sequelize.literal to construct a custom condition with plain SQL. Something like this:

const users = User.findAll({
  where: Sequelize.where(Sequelize.literal(`(SELECT COUNT(*) FROM user_roles as uroles
join organization_roles as oroles on uroles.role_id=oroles.id
where uroles.user_id=users.id and oroles.name=$name)`), '=', 0),
  bind: {
    name: 'Guardian'
  }
})
user89M50
Related