Here, I'm trying to order an associated model and limit it to 10 records. I'm using sequelize version 5.19.1 and sequelize-cli version 5.5.1. I've tried it to order without limiting the records and it worked absolutely fine, while working with the limit it does not order the way it should work. It just limits the records to 10 which is expected while does not order those records simultaneously.

Have tried using subquery: false and separate: true options but it didn't work in this case.

Associations

surgery

surgery.hasMany(models.surgeryDoctors);

surgery.belongsTo(models.species, {
  foreignKey: 'speciesId',
  targetKey: 'id',
});

surgery.hasMany(models.surgerySpecialities);

surgery doctor

surgeryDoctors.belongsTo(models.doctors, {
  foreignKey: 'doctorId',
  targetKey: 'id',
});

surgerySpecialities.belongsTo(models.doctorSpecialities, {
  foreignKey: 'specialityId',
  targetKey: 'id',
});

Query

// here db comprises the sequelize it self.

const { rows, count } = await db.surgery.findAndCountAll({
  include: [
    {
      model: db.surgeryDoctors,
      include: [
        {
          model: db.doctors,
        },
      ],
    },
    {
      model: db.species,
    },
    {
      model: db.surgerySpecialities,
      include: [
        {
          model: db.doctorSpecialities,
        },
      ],
    },
  ],
  order: [['species','name','ASC']],
  limit: 10,
});

Is there any way to fix it or any better alternative in order to achieve this? (except raw query)