0

Sequelize version: 6.6.2

Context

I have 3 models: User, Food, UserFoods. They have a Super Many-to-Many relationship between them using the documentation.

const User = sequelize.define('user', {
  username: DataTypes.STRING,
});

const Food = sequelize.define('food', {
  name: DataTypes.STRING
});

const UserFoods = sequelize.define('user_foods', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
    allowNull: false
  },
  status: DataTypes.INTEGER,
});

User.belongsToMany(Food, { through: UserFoods });
Food.belongsToMany(User, { through: UserFoods });
User.hasMany(UserFoods);
UserFoods.belongsTo(User);
Food.hasMany(UserFoods);
UserFoods.belongsTo(Food);

The status column in UserFoods will receive a number when adding a food to a user that will be used to create the user food lists (favorite, good, suck...).

This way I can query like this:

const foods = await UserFoods.findAll({
  where: { userId: req.userId, status: 1 },
  include: Food,
});

The Problem

Now I'm trying to query all Foods that are not in any of the user lists. Im doing the following:

const foods = await Food.findAll({
  where: {
    "$user_foods.userId$": { [Op.not]: req.userId },
  },
  include: {
    model: UserFoods,
    duplicating: false,
  },
  limit: 10,
});

// output foods: []

Why am I receiving an empty array?

If I change the Op.not to Op.eq it will return all the foods that belongs to that user, so why doesn't work the other way around?

Nuno Machado
  • 21
  • 5

2 Answers2

0

Use Op.ne insead of Op.not

where: {
    "$user_foods.userId$": { [Op.ne]: req.userId },
  },
Rahul Kumar
  • 3,009
  • 2
  • 16
  • 22
0

I found out a question about SQL Joins and it suggests using null on the key of the second table.

const foods = await Food.findAll({
  where: {
    [Op.or]: [
      { "$user_foods.userId$": { [Op.ne]: req.userId } },
      { "$user_foods.userId$": { [Op.is]: null } },
    ]
  },
  include: {
    model: UserFoods,
    duplicating: false,
  },
  limit: 10,
});

So for the Foods that have users associated, return only foods that are not associated with the current user.

OR

Return all Foods that does not have any associated user.

This is working but i'm not sure if is the best way to do it since I don't have much experience with Sequelize and SQL.

Nuno Machado
  • 21
  • 5