How i can get count of related rows with many-to-many relation with condition in junction table

Question

I have a problem with sequelize v6. I have two tables with many-to-many relation: User and Pet (with id as uuidv4 fields). In junction table i have two foreign keys and one custom parameter.

How i can get all pets, that related for one user (by user.id) with custom condition from junction table?

Schemas

// User
{
  id: {
    type: DataTypes.UUID,
    defaultValue: DataTypes.UUIDV4,
    primaryKey: true,
  },
  ...
}

// Pet
{
  id: {
    type: DataTypes.UUID,
    defaultValue: DataTypes.UUIDV4,
    primaryKey: true,
  },
  ...
}

// UserPet
{
  userId: DataTypes.UUID,
  petId: DataTypes.UUID,
  owner: DataTypes.BOOLEAN,
}

Associate

// UserPet
static associate() {
  User.belongsToMany(Pet, { through: UserPet });
  Pet.belongsToMany(User, { through: UserPet });
}

Class methods

// User
declare getPets: BelongsToManyGetAssociationsMixin<Pet>;
declare addPet: BelongsToManyAddAssociationMixin<Pet, string>;
declare addPets: BelongsToManyAddAssociationsMixin<Pet, string>;
declare setPets: BelongsToManySetAssociationsMixin<Pet, string>;
declare removePet: BelongsToManyRemoveAssociationMixin<Pet, string>;
declare removePets: BelongsToManyRemoveAssociationsMixin<Pet, string>;
declare hasPet: BelongsToManyHasAssociationMixin<Pet, string>;
declare hasPets: BelongsToManyHasAssociationsMixin<Pet, string>;
declare countPets: BelongsToManyCountAssociationsMixin;

What i used, but it not worked:

const user = await User.findOne(1);

// For one pet with condition
user.getPet({
  where: { 
    [Op.and]: { id: req.params.petId, owner: true },
  },
});

// For many pets with condition
user.getPets({
  where: { 
    owner: true,
  },
});

Thanks!

Answer 1

You need to update the association first and ensure that the models are correctly defined.

User.belongsToMany(Pet, { through: UserPet });
Pet.belongsToMany(User, { through: UserPet });

UserPet.belongsTo(User, { foreignKey: 'userId' });
UserPet.belongsTo(Pet, { foreignKey: 'petId' });

User.hasMany(UserPet, { foreignKey: 'userId' });
Pet.hasMany(UserPet, { foreignKey: 'petId' });

"To get all pets, that related for one user (by user.id) with custom condition from junction table" =>

We have to join the User and Pet tables using the User ID based on the UserPet table.

Here, the condition object is used to filter the UserPet junction table.

async function getAllPetsByUserID(userId, condition) {
  const userPets = await User.findOne({
    where: { id: userId },
    include: {
      model: Pet,
      through: {
        where: condition, // { owner: true }
        attributes: [],
      },
    },
  });

  return userPets ? userPets.pets : [];
}